vba - Importing Data from specific excel sheets from multiple workbooks in a folder

Question

I need to pull the data from specific sheets in multiple excel workbooks into a master copy. I've managed to make one which pulls from every sheet in the workbooks but cant figure out how to make it pull the data from a specified sheet. My code is below:

    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:UsersMatthew.Stokes.HugheDesktop	est 2Temp")

    'Next available Row on Master Workbook
    y = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

    '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) = "xlsx" 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("sheet1").Cells(y, 1) = ws.Cells(x, 1) 'col 1
                ThisWorkbook.Sheets("sheet1").Cells(y, 2) = ws.Cells(x, 2) 'col 1
                ThisWorkbook.Sheets("sheet1").Cells(y, 3) = ws.Cells(x, 3) 'col 1
                ThisWorkbook.Sheets("sheet1").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

The Name of the Specified sheets to draw the information from is Sheet 1. Any help would be great!

Answer 1

You just need to an if-statement to check the name:

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:UsersMatthew.Stokes.HugheDesktop	est 2Temp")

  'Next available Row on Master Workbook
  y = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

  '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) = "xlsx" Then

        'Open current book
        Set wb = Workbooks.Open(wbFile.Path)

        'Loop through each sheet (ws)
        For Each ws In wb.Sheets
  
          'check WS name
          If UCase(ws.Name) = "DATA" Then
  
        '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
            
              Dim c As Long
              For c = 1 To 4
              '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("sheet1").Cells(y, c) = ws.Cells(x, c) 'col 1
              Next c
              
              y = y + 1
            Next x
          
          End If
          Next ws

        'Close current book
        wb.Close
      End If

  Next wbFile

  End Sub