Merge particular sheets from multiple workbooks

Question

I have a folder with 8 workbooks with multiple sheets. I want to rearrange columns from the sheet named RAW from all workbooks and combine all the RAW sheets into one sheet as Final_Raw. I need a macro code to achieve this also can this be automated using python?

Answer 1

It is possible to do in VBA. You need to collect the data from the sheets. This means you declare all the sheets like:

Sub getdata()

    Dim strLocation As String
    Dim objWorkbookOne As Workbook
    Dim wsData As Worksheet
    Dim intFR, intLR As Integer
    strLocation = "C:\Users\fredd\Documents\"
    
    Set objWorkbookOne = Workbooks.Open(strLocation & "14082022194559_download_MEDEWERKER.xlsx")
    Set wsData = ThisWorkbook.Sheets(1)
    wsData.Activate
            
    intFR = 1
    intLR = objWorkbookOne.Worksheets("MEDEWERKER").Cells(Rows.Count, 1).End(xlUp).Row
                
    For intFR = 1 To intLR
    
        wsData.Cells(intFR, 1) = objWorkbookOne.Worksheets("MEDEWERKER").Cells(intFR, 1)
            
    Next intFR
    
End Sub

In the code above we get data from a file named 14082022194559_download_MEDEWERKER.xlsx on location *C:\Users\fredd\Documents*. I made a variable of the location so it is easy to change if nessesary. The file is opened in objWorkbookOne (ofcourse you can do this for eight workbooks as well).

When the workbook is opened, we activate the sheet in which we want to 'paste' the data. Next the first row (intFR) and last Row (intLR) are defined in workbookone. With that FOR loop you can 'copy' the data to the masterfile.

I don't know exactly how your masterfile and other files are build up, so the I have to make assumptions. In this code (above) I copy one column to another column, but this is also possible with ranges.