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
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:
How to use a macro to run through sheet in excel and put double quotes around values |
How to use a macro for grouping rows based on cells with same names |
How to use a macro to pull every Nth row of data |
How to use a macro to select value criteria from a table rather than manually inputting |
How to create a macro button to put date in selected cell within specific column |
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.