0

I have a folder containing several files. I also have one masterfile. I want to assign a value from each file to a range in the masterfile. For example, if the file contains the word "Chocolate" in A1, I want it to appear 5 times in the Masterfile from C1:C5.

I noticed that if I do not use the Workbook.Activate function, my code does not work, if I use the Range(Cells(),Cells()) format to specify the range in the Masterfile to copy to. I was wondering if someone could provide me a reason for this, as I heard that one should stay away from this function as far as possible.

Here is my code:

Sub AddDataToMasterfile()

Dim wb As Workbook 'Workbook from which I want to copy
Dim masterWb As Workbook 'Masterfile to which I want to paste
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

Set masterWb = ThisWorkbook 'macro will be stored in module in Masterfile

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
 If .Show <> -1 Then GoTo NextCode
 myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo Done
'Target File Extension (must include wildcard "*")
myExtension = "*.xls*"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""

'Set variable equal to opened workbook from which to copy
Set wb = Workbooks.Open(Filename:=myPath & myFile)

'Set value in Masterfile range equal to value in one cell in wb
masterWb.Activate 'error if I do not include this and use cells range format as below

masterWb.Worksheets("Federer").Range(Cells(1, 1), Cells(5, 1)).Value = wb.Worksheets(1).Range("A1") 
'works if I change Range format to "A1:A5", regardless of 'activate' function above

'Close Workbook
wb.Close SaveChanges:=False

'Get next file name
myFile = Dir

Loop

Done:
MsgBox "Import complete"

End Sub

I know that in my example each loop will override the current values in my Masterfile, but is not relevant to my question regarding Workbook.Activate

K2SO
  • 23
  • 5

1 Answers1

0

Your Cells references are not fully qualified, so they assume the active sheet, which is why you need that extra line of code.

You should be able to change it to this safely and remove the Activate line.

With masterWb.Worksheets("Federer")
    .Range(.Cells(1, 1), .Cells(5, 1)).Value = wb.Worksheets(1).Range("A1") 
End With

Note: a Cells reference within a Range does not automatically assume you want to reference the same Worksheet, even tho logically it seems like it should.

braX
  • 11,506
  • 5
  • 20
  • 33