VBA to copy data from multiple workbooks into master file

DangerSloth

New Member
Joined
Sep 11, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,203
Office Version
  1. 365
Platform
  1. Windows
Hello DangerSloth,

See if the following code does the task for you:-
VBA Code:
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

New Member
Joined
Sep 11, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hello DangerSloth,

See if the following code does the task for you:-
VBA Code:
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.

VBA Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,203
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 11, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
Just use
VBA Code:
wb.Close False
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,203
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top