'Runtime Error '9' Subscript Out Of Range: VBA Code to Append data from Multiple Workbooks into a single Workbook
I have about 10 workbooks in a testing folder. All of them have a sheet labeled Detail. I need to copy out data from that sheet in the range B21:BT (stop after hitting a blank row-- as there the number of rows can change for each workbook.) I would like to paste all of this data into a workbook that is already created labeled Master.
Here is what I have so far but im hitting a runtime '9' subscript out of range error:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Const strPath As String = "G:\GLOBALPRGM\STAFF\Testing\"
ChDir strPath
strExtension = Dir("*.xlsx*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
.Sheets("Detail").Range("B20:BT" & .Sheets("Detail").Range("B" & Rows.Count).End(xlUp).Row).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
any help cracking this one is much appreciated.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
