Pulling Data from Multiple workbooks to one consolidated sheet

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

Hi,

Can you try this instead :

y = ThisWorkbook.Sheets("Data").Cells(ThisWorkbook.Sheets("Data").Rows.Count, 1).End(xlUp).Row + 1 '<-------- For this line i keep getting runtime error '9', subscript out of range

Yes it's possible. But can you clarify which data you want to pull from those workbook and copy to the master workbook?
Is it only the last row?
all rows?
12 columns?

Thanks,

(Having a sample workbook would help...)

Like this?

Sub getDataFromWbs()
   Dim wbM As Workbook
   Dim wb As Workbook, ws As Worksheet
   Dim fldr, wbFile

   'Set the Master workbook
   Set wbM = ThisWorkbook

   Set fso = CreateObject("Scripting.FileSystemObject")

   'This is where you put YOUR folder name
   Set fldr = fso.GetFolder("C:\Users\<My name>\Desktop\Temp\")

   '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)
      
         'Look only in sheets DATA
         With wb.Sheets("Data")
                
            'Assuming that you want copy the last row / 12 columns
            'to the next row in sheets "DATA" of the Master workbook:
            .Range("A66666").End(xlUp).Resize(1, 12).Copy wbM.Sheets("Data").Range("A66666").End(xlUp).Offset(1, 0)
         
         End With
      
         'Close current book
         wb.Close
      End If

   Next wbFile

End Sub

Hi,

Here's a revised code.
it is not clear which columns you want to copy to your master workbook.
As an example, I put columns 11-12-13-14. Change as required.

Sub getDataFromWbs2()
   Dim wbM As Workbook
   Dim wb As Workbook, ws As Worksheet
   Dim fldr, wbFile, fso
   Dim Lastrow As Long
   
   Application.ScreenUpdating = False

   'Set the Master workbook
   Set wbM = ThisWorkbook

   Set fso = CreateObject("Scripting.FileSystemObject")

   'This is where you put YOUR folder name
   'Set fldr = fso.GetFolder("C:\Users\<My name>\Desktop\Temp\")
   Set fldr = fso.GetFolder("C:\Temp\")

   '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)
      
         'Look only in sheets DATA
         With wb.Sheets("Data")
         
            'last row master workbook
            Lastrow = wbM.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
            
            'copy to master workbook columns 11-12-13-14  (adapt as required)
            wbM.Sheets(1).Cells(Lastrow, 1) = .Cells(2, 11)   'Name
            wbM.Sheets(1).Cells(Lastrow, 2) = .Cells(2, 12)   'Title
            wbM.Sheets(1).Cells(Lastrow, 3) = .Cells(2, 13)   'Date
            wbM.Sheets(1).Cells(Lastrow, 4) = .Cells(2, 14)   'Sales Amount
            
         End With
      
         'Close current book
         wb.Close
         
      End If
      
   Next wbFile
   
   Application.ScreenUpdating = True

End Sub