VBA to copy data from multiple workbooks into master sheet

Question

I have to copy data from 100+ workbooks and paste it into a master workbook. All the workbooks are located in a folder on my desktop: C:\Users\brw20\Desktop\Project XYZ\Updated\

All the workbooks contain a sheet named ‘Mutation', I have to open each workbook, go to sheet 'Mutation’, select columns range A to J starting from row 3 to row ‘x'(the last row can vary in each workbook). In the master worksheet, I paste the data in Columns A to J and continue pasting/appending the data as I copy data from more workbooks. The code given below I tried has fixed no. of rows due to which pop up generates(“There is large amount of information on clipboard. Do you want to be able to paste this information into another program later.”) against each copying sheet. So, I was wondering if someone could please help me to create a VBA code for this which copy only fixed columns but variable rows? I'm really new to VBA and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks! =)

Sub LoopThroughDirectory()
Dim MyFile As String
Dim wb As Workbook
Dim ws As Worksheet

sPath = "C:\Users\brw20\Desktop\Project XYZ\Updated\"
Dim erow
MyFile = Dir(sPath & "*.xlsx")
Application.ScreenUpdating = False
Do While MyFile <> ""
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Workbooks.Open (sPath & MyFile)
'Set ws = wb.Sheets(2)
Worksheets("Mutation").Range("A3:J1000").Copy
'Call CopyValues(Sheet1.Range("A1:B12"), Sheet1.Range("A1:B12"))
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 1))
'Workbooks.Open ThisWorkbook.Path & "\DgetData.xls"
MyFile = Dir
Loop

End Sub

Today Excel provides a tool that will allow you to do this work without writing VBA code. Look in the Data tab for the button Get Data - From File - From Folder. The whole operation will take you 10 or 15 minutes. There are many videos on YouTube that show how this is done. — JohnSUN

AcsErno AcsErno · Accepted Answer · 2020-09-02T13:40:58

Try to avoid using ActiveWorkbook and ActiveSheet, and use direct references instead, like this:

Set wbMast = Workbooks.Open(ThisWorkbook.Path & "\DgetData.xls")
Set wbSrc = Workbooks.Open (sPath & MyFile)

This command (in 2 variants) dynamically selects source and destination ranges for copy by finding the last non-empty cells both in source and destination ranges:

Range(wbSrc.Worksheets("Mutation").cells(1, 3), _ 
      wbSrc.Worksheets("Mutation").Cells(100000, "J").end(xlup)).Copy  _
         destination:=wbMast.Sheets(1).Cells(1,1).End(xlUp).Offset(1,0)

or a more compressed variant using With

   With wbSrc.Worksheets("Mutation")
        Range(.cells(1, 3), .Cells(100000, "J").end(xlup)).Copy  _
            destination:=wbMast.Sheets(1).Cells(1,1).End(xlUp).Offset(1,0)
   End With

(This one checks column A in wbMast and column J in wbSrc for last non-empty cell.)

If you still get message on "... large amount of information ...",
```
Application.DisplayAlerts = False
```

will probably suppress the message.

VBA to copy data from multiple workbooks into master sheet

1 Answers