VBA to copy same range from multiple files onto multiple sheets in a summary file

[Elinda]

I have a working file which contains 10 sheets (sheet names represent companies). There is a summary sheet containing pivots of the companies’ data. Each pivot uses name manager for source data because this way Refresh can work even if the number of rows vary from one month to the other (number and order of columns is always the same). There is a folder in which there are 10 files containing raw data and the range I need is always on the first sheet. File names include the name of the relevant company. What I would like is to create a macro which can open each file from the folder and copy range A:AF & last row and paste it to the sheet with the same company name.

I know how to do this with one file but I don’t know how to “tell” vba to open seversl files and recognize to which sheet in the working file it should copy the data from the raw data file.

For the sake of simplicity let’s call the companies: A, B, C, …, J. Let’s call raw data files: Company A, Company B, etc. And let’s call the sheets in the working file: A, B, C, etc.

Could someone help me please?

 

[John_w]

Try this macro, after changing the rawDataFolder string. Assumes the summary sheet is named "Summary" (case-insensitive) and the raw data files are .xlsx files.

Public Sub Import_Range_From_Company_Workbooks_In_Folder()

    Dim rawDataFolder As String
    Dim ws As Worksheet
    Dim companyWorkbook As Workbook
    Dim rawDataRange As Range

    rawDataFolder = "C:\path\to\raw\data\"
    If Right(rawDataFolder, 1) <> "\" Then rawDataFolder = rawDataFolder & "\"
    
    Application.ScreenUpdating = False
    
    With ActiveWorkbook
        For Each ws In .Worksheets
            If StrComp(ws.Name, "Summary", vbTextCompare) <> 0 Then
                Set companyWorkbook = Workbooks.Open(rawDataFolder & "Company " & ws.Name & ".xlsx")
                With companyWorkbook.Worksheets(1)
                    Set rawDataRange = .Range("A1", .Cells(.Rows.Count, "AF").End(xlUp))
                End With
                rawDataRange.Copy ws.Range("A1")
                companyWorkbook.Close False
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

 

[Elinda]

Try this macro, after changing the rawDataFolder string. Assumes the summary sheet is named "Summary" (case-insensitive) and the raw data files are .xlsx files.

Public Sub Import_Range_From_Company_Workbooks_In_Folder()

    Dim rawDataFolder As String
    Dim ws As Worksheet
    Dim companyWorkbook As Workbook
    Dim rawDataRange As Range

    rawDataFolder = "C:\path\to\raw\data\"
    If Right(rawDataFolder, 1) <> "\" Then rawDataFolder = rawDataFolder & "\"
   
    Application.ScreenUpdating = False
   
    With ActiveWorkbook
        For Each ws In .Worksheets
            If StrComp(ws.Name, "Summary", vbTextCompare) <> 0 Then
                Set companyWorkbook = Workbooks.Open(rawDataFolder & "Company " & ws.Name & ".xlsx")
                With companyWorkbook.Worksheets(1)
                    Set rawDataRange = .Range("A1", .Cells(.Rows.Count, "AF").End(xlUp))
                End With
                rawDataRange.Copy ws.Range("A1")
                companyWorkbook.Close False
            End If
        Next
    End With
   
    Application.ScreenUpdating = True
   
    MsgBox "Done"
   
End Sub

Thank you so much, it worked ))))

 

[Georgiboy]

Power Query is great at this, I use PQ to get the data and then process it further with VBA. PQ can pull any number of files from a folder and you can specify the sheet to return etc. Returns the data in one table which can be refreshed if new files are added/ amended.

 

[Elinda]

Power Query is great at this, I use PQ to get the data and then process it further with VBA. PQ can pull any number of files from a folder and you can specify the sheet to return etc. Returns the data in one table which can be refreshed if new files are added/ amended.

Thank you for the idea, I only use PQ for a very basic excercise but it seems like I should learn more about it