1

I'm trying to copy two cells B2 & C2 from the Results worksheet on every single workbook within a folder and then paste cells in a Master workbook starting in cell A1, A2, A3 etc

I'm getting the error Subscript out of range it isn't highlighting anything specific but I'm assuming it's because the workbook the macro is running from doesn't have a 'Results' sheet.

It's opening the correct workbook Workbooks.Open (Filepath & MyFile) but I don't seem to be able to set the newly opened workbook as the ActiveWorkbook to copy from and then close.

Thanks

    Sub LoopThroughDirectory()

Dim MyFile As String
Dim WorkbookCounter As Long
WorkbookCounter = 1
Dim Filepath As String
Dim wb As Workbook

Filepath = "C:\Test\"

Application.ScreenUpdating = False
MyFile = Dir(Filepath)

'Opens workbooks located C:\Test\ in order
Do While Len(MyFile) > 0
Set wb = Workbooks.Open(Filepath & MyFile)

'Copy cells B2 & C2 from the results worksheet
wb.Worksheets("Results").Range("B2:C2").Copy
Application.DisplayAlerts = False

'Paste cells B2 & C2 to A1
Sheets(WorkbookCounter).Select
ActiveSheet.Paste Destination:=Worksheets(WorkbookCounter).Range("A1")
wb.Close SaveChanges:=False

Application.CutCopyMode = False
WorkbookCounter = WorkbookCounter + 1
If WorkbookCounter > 1000 Then
Exit Sub
End If

MyFile = Dir
Loop
ActiveWorkbook.Save
Application.ScreenUpdating = True

End Sub
  • You should really [avoid using `.Select` and `.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) anyways. What you can do is make a `Dim currentWorkbook as Workbook` variable and when you open a new book, say `set currentWorkbook = workbooks.open(...)` – Marcucciboy2 Sep 13 '18 at 20:43
  • Then you could use a function to check if the sheet `Results` exists in the `currentWorkbook` with this https://stackoverflow.com/a/6688482/2727437 – Marcucciboy2 Sep 13 '18 at 20:45

1 Answers1

1

Workbooks.Open is a function, that returns a reference to the Workbook object that was opened - and you're discarding it.

Declare a Workbook variable.

Dim wb As Workbook

Then assign it to the result of the Workbooks.Open call:

Set wb = Workbooks.Open(Filepath & MyFile)

Now wb is the workbook object you work with - whether it's active or not, doesn't matter anymore.

wb.Worksheets("Results").Range("B2:C2").Copy

'NOTE: paste to destination BEFORE closing the workbook

wb.Close SaveChanges:=False
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thanks a lot but I think you're giving me too much credit with this one, I should've said I was following a tutorial on youtube to get this far. I've managing to open the first workbook in the folder (correctly!) and copy cells B2:C2 (I can see the open workbook with the cells highlighted and copied). It's getting stuck pasting, cell A1 populates as I'd expect but cell A2 doesn't and I'm getting the error "Application-defined or object-defined error" I'll update the original post with what I've got now. –  Sep 13 '18 at 21:03
  • @L23P it's not clear what workbook the destination sheet is supposed to be in. qualify it with the proper workbook object. Also you're copying `B2:C2`, and pasting into `A1`, so I'd expect it to populate `A1:B1` and leave `A2` alone. Try resizing the destination range to match the source. – Mathieu Guindon Sep 13 '18 at 21:08
  • I tried this and now it's not copying and giving me a subscript out of range error. wb.Worksheets("Results").Range("B2:C2").Copy ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1:B1") –  Sep 13 '18 at 21:22
  • Avoid `ActiveSheet` references, work with worksheet objects. The code will be much easier to follow, and much more robust. Try `wb.Worksheets("Results").Range("B2:C2").Copy Destination:=destinationSheet.Range("A1:B1")` – Mathieu Guindon Sep 13 '18 at 21:24
  • Got you, avoid ActiveSheet, does that means ActiveWorkbook.Save then right towards the end? I'm actually not even sure I need the ActiveWorkbook.save at the end at all. I can just hit save myself rather than hardcoding it. New error with the above suggestion: Expected: expression. It doesn't like ":=" –  Sep 13 '18 at 21:47