Copy values from different workbooks into a master file

So, at my work I am trying to create a master file which copies information from a fixed cell from different Excel files into a Master file. these values need to be written in different cells that are in the same row, but different column. My code can read through the files but does not paste any values in the cells, here is my code:

Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "" & MyFile, UpdateLinks:=False
Range("J23").Select
Selection.Copy
Windows("MasterFile1.xlsm").Activate
For c = 2 To 13
Cells(4, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next c
0
Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir
Loop


What worksheet is J23 on in the recently opened workbook? What worksheet is the destination on the master workbook? Do you really want to put the same J23 value into B4:M4 in the master workbook?
– Jeeped
Jun 29 at 9:00

J23 is in the first Worksheet of different workbooks and each of these files has a different value in J23
– Gerardo Lopez
Jun 29 at 9:10

You seem to be batting .333 on the questions.
– Jeeped
Jun 29 at 9:12

You are overwriting B4:M4 with the new J23 value each time you open a different workbook. Did you want to put the first J23 into B4 then the second workbook's J23 into C4 etc?
– Jeeped
Jun 29 at 9:14

1 Answer
1

You should know what worksheet you are using from the external source workbook and what worksheet you are planning for the destination within the master workbook.

No need to loop. You can write a single value into as many cells as you wish.

No need for Copy & Paste Special if you are only concerned with the value; a direct value transfer is faster and doesn't involve the clipboard.

Dim mwb As Workbook

Set mwb = Workbooks("MasterFile1.xlsm")

Do While MyFile <> ""
On Error GoTo 0
With Workbooks.Open(Filename:=MyFolder & "" & MyFile, UpdateLinks:=False, ReadOnly:=True)
mwb.Worksheets(1).Range(.Cells(4, "B"), .Cells(4, "M")) = _
.Worksheets(1).Range("J23").Value
.Close SaveChanges:=False
End With
MyFile = Dir
Loop


In an alternate scenario where you want different external workbooks' J23 value to go into B4, C4, D4, etc in the master workbook, then this might be more suitable.

Dim mwb As Workbook, o As Long

Set mwb = Workbooks("MasterFile1.xlsm")

Do While MyFile <> ""
On Error GoTo 0
With Workbooks.Open(Filename:=MyFolder & "" & MyFile, UpdateLinks:=False, ReadOnly:=True)
mwb.Worksheets(1).Cells(4, "B").Offset(Int(o / 12), o Mod 12) = _
.Worksheets(1).Range("J23").Value
o = o + 1
.Close SaveChanges:=False
End With
MyFile = Dir
Loop


Thank you, Jeeped. Is it possible to skip row every 12 workbooks opened? I have looked for something to do that and have not found it yet
– Gerardo Lopez
Jun 29 at 9:59

So the 13th J23 would go into B5, 14th into C5, etc?
– Jeeped
Jun 29 at 10:05

Yes, and 25th into B6, etc etc.
– Gerardo Lopez
Jun 29 at 10:08

Just some simple maths; fixed above.
– Jeeped
Jun 29 at 10:18

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.