All sheets have different sheet name, but want to loop through, and add into one master sheet

Question

This is my current code, I don't want to add sheet name where I am getting error, because all workbooks have one sheet and all have a different sheet name. I want to combine 42 sheets into one, but only want to copy the rows after the heading in each sheet

Sub CopytoOneSheet()
Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Users\me\OneDrive - Company\New folder\" 
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .ActiveSheet.Range("A2:S" & LastRow).Copy wkbDest.Sheets("All_TripSum").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) '**Getting run-time error '9': Subscript out of range here**
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Have a look for some ideas: https://stackoverflow.com/q/30575923/4961700 Many q&a about this topic or looping on here... — Solar Mike, Jul 07 '22 at 17:07
are you sure you're getting an error at `Const strPath As String =...` running the code to here doesn't throw an error (obviously i don't know if that's a usable path) — InjuredCoding, Jul 07 '22 at 17:09
Updated to show which row I am getting error on, path is usable — pjpj, Jul 07 '22 at 18:22
Do you need the formatting of the source cells - or do you need values only? Are there empty cells within the copied range? — Ike, Jul 07 '22 at 18:24

score 0 · Answer 1 · answered Jul 07 '22 at 21:11

I went from using ActiveSheet to calling the sheet by it's position Sheet(1)

Instead of using ThisWorkbook, I used the name of my file.

Also, I added Application.CutCopyMode = False to avoid getting the pop up each time a workbook closes.

Though this may not be an efficient way, it did work for me. Please feel free to respond, I will probably be using this fairly often, and would like to make this more efficient and keep learning

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = Workbooks("All_TripSum.xlsx")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\me\OneDrive - Company\New folder\" 
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets(1).Range("A2:S" & LastRow).Copy
            wkbDest.Sheets("All_TripSum").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

All sheets have different sheet name, but want to loop through, and add into one master sheet

1 Answers1