OzGrid

How to use a Macro to copy data from multiple workbooks to one master sheet in another workbook

Requirement:

The user needs a macro that will:

1) Open first workbook containing data (see sample image for dummy data).
2) Copy data from A2 to the final column containing data and down to the last row in the data set.

This won't be a set number of rows (because it can change depending on how many entries are in the spreadsheet, but will be a set number of columns. However, for the sake of simplicity and flexibility of using this for more than one task, the user would like it to copy from A2 to the last row and final column with data.

The user is dealing with about 220+ files and the macro that was created for this sort of thing at work isn't working.

3) Paste copied data into master spreadsheet.
4) Close first workbook
5) Open second workbook
6) Copy data from A2 to the final column containing data and down to the last row in the data set.
7) Paste copied data into master spreadsheet under the first copied data set.
8) Repeat the above steps until all files have been processed.

Solution:

Place this macro in a standard module in the Master workbook and run it from there. The macro assumes that all the source files are in the same folder, have an extension of "xlsx", the data to be copied is on "Sheet1" and they are the only files in that folder. It also assumes that the Master workbook has a sheet named "Master" where all the data from the source workbooks will be pasted. Change the data in red (click on like below to go to forum to review 'red' items) to suit your needs including the folder path.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, wsDest As Worksheet
    Set wsDest = ThisWorkbook.Sheets("Master")
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Sheet1").UsedRange.Offset(1, 0).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1214317-macro-to-copy-data-from-multiple-workbooks-to-one-master-sheet-in-another-workbook

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery