VBA to copy data from multiple workbooks into master file

[DangerSloth]

Hi!

I am fairly new to VBA so I am having trouble in creating a code.

I did found one here but I don't think it is specifically tailored to what I need.

In simple words, I need a code to copy the data from all the files in the folder to my master file. All files only have 1 sheet of them and the number of data will vary per file. So I will need to use a code that will find the last column and row that has data and copy it to the master file. Then the next file that will be copied will start in the next row where the last data from the previous file stopped pasting (i hope this makes sense).

Also, the master file has a header on Row 1 and should not over written. The source files also has headers on row 1 which should not be copied, so the code should start copying from "A2" to the last column and row. of the source files.

I found this code here courtesy of a Mr. Mumps bat that was years ago and might not work now and this might not be tailored to what I need.

I hope someone can help me.

Sub CopyRange()

    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "Z:\DoX\CopyFiles Test\Source Files"
    ChDir strPath
    Dim strExtension As String
    
    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Sheet1").Range("A2:O" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
    
End Sub

 

[vcoolio]

Hello DangerSloth,

See if the following code does the task for you:-

Sub Test()

            Dim stgF As String, stgP As String
            Dim wb As Workbook
            Dim ws As Worksheet
            Set ws = ThisWorkbook.Worksheets("Master") 'Assuming that "Master" is the destination sheet name.
        
            stgP = "C:\Users\YOUR FILE PATH HERE"  '---->Insert your file path.
            stgF = Dir(stgP & "\*.xls*")
        

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

            Do While stgF <> vbNullString
            
                    Set wb = Workbooks.Open(stgP & "\" & stgF)
                
                    With wb.Sheets(1)
                            .UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)
                            ws.Columns.AutoFit
                    End With
                
                            wb.Close Save = False
                            stgF = Dir()
            Loop

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Just change the file path to suit.

I hope that this helps.

Cheerio,
vcoolio.

 

[DangerSloth]

Hello DangerSloth,

See if the following code does the task for you:-

Sub Test()

            Dim stgF As String, stgP As String
            Dim wb As Workbook
            Dim ws As Worksheet
            Set ws = ThisWorkbook.Worksheets("Master") 'Assuming that "Master" is the destination sheet name.
       
            stgP = "C:\Users\YOUR FILE PATH HERE"  '---->Insert your file path.
            stgF = Dir(stgP & "\*.xls*")
       

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

            Do While stgF <> vbNullString
           
                    Set wb = Workbooks.Open(stgP & "\" & stgF)
               
                    With wb.Sheets(1)
                            .UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)
                            ws.Columns.AutoFit
                    End With
               
                            wb.Close Save = False
                            stgF = Dir()
            Loop

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Just change the file path to suit.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio!

Thanks for the quick response. I tried your code and it returned in the variable not defined error.

Variable defined error is in the wb.Close Save = false line.

Moreover, do I not need to choose tick references in the tools section in the editor?

Below is the actual code I have been using. You are correct to assume that the "Master" sheet is the one where all the data should be copied.

Sub Test()

            Dim stgF As String, stgP As String
            Dim wb As Workbook
            Dim ws As Worksheet
            Set ws = ThisWorkbook.Worksheets("Master") 'Assuming that "Master" is the destination sheet name.
        
            stgP = "Z:\DoX\CopyFiles Test\Source Files\"  '---->Insert your file path.
            stgF = Dir(stgP & "\*.xls*")
        

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

            Do While stgF <> vbNullString
            
                    Set wb = Workbooks.Open(stgP & "\" & stgF)
                
                    With wb.Sheets(1)
                            .UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)
                            ws.Columns.AutoFit
                    End With
                
                            wb.Close Save = False
                            stgF = Dir()
            Loop

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[vcoolio]

Hello DangerSloth,

I can't re-create the error you are receiving and it works well with my own test files. If you look further up the code, directly after the 'Do While' part, you'll see that the wb variable is well and truly defined.
The code assumes that all your workbooks are in the same folder as the Master file.

Check that your path is correct in all aspects: spelling, cases, punctuation etc.

Moreover, do I not need to choose tick references in the tools section in the editor?

The Microsoft Office 16.0 Object Library box needs to be ticked plus the three directly above it.

Cheerio,
vcoolio.

 

[DangerSloth]

Hello DangerSloth,

I can't re-create the error you are receiving and it works well with my own test files. If you look further up the code, directly after the 'Do While' part, you'll see that the wb variable is well and truly defined.
The code assumes that all your workbooks are in the same folder as the Master file.

Check that your path is correct in all aspects: spelling, cases, punctuation etc.


The Microsoft Office 16.0 Object Library box needs to be ticked plus the three directly above it.

Cheerio,
vcoolio.

Hi Vcoolio,

Just transferred all the work to my work laptop as I thought this might not work on an older version of Excel.

I have already created the test files and the source folders.

Oddly enough, I am still getting the same "variable not defined" error. The highlighted word is the "save" word in the

wb.Close [B]Save[/B] = False

line.

Perhaps that's because it hasn't opened any files yet that's why it can't perform the close and save function?

Again, thanks for responding to my queries.

Regards,
DangerSloth

 

[Fluff]

Just use

wb.Close False

 

[vcoolio]

Hello DangerSloth,

I still don't have any problems on my end. However, I was going to suggest two more methods that you can test with:-
1) As per Fluff's suggestion or
2) Remove the wb.Close Save = False line and move it to directly above the End With line (either as you see it in the code in post #2 or with
just wb.Close False).

Cheerio,
vcoolio.