VBA Code to copy all cells from workbooks located on a specific file and paste it on master workbook

[TheLSD]

I have a master workbook that I need to update 4 sheets daily (Data A, Data B, Data C, Data D)

I get the data workbook and worksheet name differently each time so I change it manually in order for the code run that copies the first sheet of the workbook on a specified folder so I need to change it to copy the used cells only

what should I change the part of "copy only the first sheet of a workbook" part to copy and paste it on the master workbook-specific sheets (the 4 sheets above)? So for example Data1.xlsx first sheet to "Data 1" sheet on master workbook, Data2.xlsx first sheet to "Data 2" sheet on master workbook and so on.

here's my code

Sub OpenWorkbooks(sourceFolder As Variant)
    
    Dim sourceFile As String 'Filename obtained by DIR function
    Dim masterWb, Sourcewb As Workbook 'Used to loop through each workbook
    Dim ext As String
    On Error Resume Next
    
    Application.ScreenUpdating = False
    
    Set masterWb = ThisWorkbook
    sourceFile = Dir(sourceFolder & "\") 'DIR gets the first file of the folder
    Do While sourceFile <> ""
        ext = Right(sourceFile, Len(sourceFile) - InStrRev(sourceFile, "."))
        If sourceFile <> ThisWorkbook.Name And (ext = "xlsm" Or ext = "xlsx") Then
            Workbooks.Open Filename:=sourceFolder & "\" & sourceFile, ReadOnly:=True
            Set Sourcewb = ActiveWorkbook
            'Copy only the first sheet of a workbook
            Sourcewb.Worksheets(1).Copy after:=masterWb.Sheets(masterWb.Worksheets.Count)
            'Close the source workbook
            Sourcewb.Close SaveChanges:=False
            Set Sourcewb = Nothing
        End If
        sourceFile = Dir 'DIR gets the next file in the folder
    Loop
    
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[saroj123]

Hi,

This code is somewhat similar to what I need but I need some changes to it. Please help.

1. I have 65 workbooks with customer details in a folder.
2. All the workbooks are of the same format and name of worksheets in each workbook have same name. There are 8 worksheets in each workbook.
3. So supposing I need to pull only value of cell "B15", & "C15" of Sheet 1, "B15" , "A20" of sheet 2 of the first excel Workbook in the folder.
4. And this goes in Loop till it pulls data from all the workbooks in the folder.
5. All this data comes to my Mastersheet where I put it in a row. So logically each sheet will have one row of data starting from first till 65.

Please suggest a code.