So I'm trying to pull the data from a specific sheet in a list of work books into one master sheet. The data is in the sheet "Data", and per workbook there is one row and 12 columns. the current formula I have is(I've included the error next to it related line):
Sub getDataFromWbs()
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
'This is where you put YOUR folder name
Set fldr = fso.GetFolder("C:\Users\<My name>\Desktop\Temp\")
'Next available Row on Master Workbook
y = ThisWorkbook.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 <-------- For this line i keep getting runtime error '9', subscript out of range
'Loop through each file in that folder
For Each wbFile In fldr.Files
'Make sure looping only through files ending in .xlsx (Excel files)
If fso.GetExtensionName(wbFile.Name) = "xls" Then
'Open current book
Set wb = Workbooks.Open(wbFile.Path)
'Loop through each sheet (ws)
For Each ws In wb.Sheets
'Last row in that sheet (ws)
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
'Loop through each record (row 2 through last row)
For x = 2 To wsLR
'Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
ThisWorkbook.Sheets("Data").Cells(y, 1) = ws.Cells(x, 1) 'col 1
ThisWorkbook.Sheets("Data").Cells(y, 2) = ws.Cells(x, 2) 'col 1
ThisWorkbook.Sheets("Data").Cells(y, 3) = ws.Cells(x, 3) 'col 1
ThisWorkbook.Sheets("Data").Cells(y, 4) = ws.Cells(x, 4) 'col 1
y = y + 1
Next x
Next ws
'Close current book
wb.Close
End If
Next wbFile
End Sub
Bookmarks