How to copy data from Multiple workbooks into one
Category: [Excel] Demo Available
How to copy data from Multiple workbooks into one
Requirement:
The user has a few workbooks & wants to move the data from the different workbooks into one master file.
The files are in different location & all have the same headers.
The user wants to be able to copy the data & paste into the master without overlapping into the other data & also do a paste special while pasting it into the master file.
The files are in different folder & the master in a different.
All the data in the files are stored in sheet 1 & the user wants the entire sheet data to be placed in sheet 1 of master files, but i don't want the data to be overlapped with the others.
The data from the source file should be placed as values in the master excel.
Solution:
Place this macro in a regular module in your destination workbook and run it from there. Make sure this workbook contains a sheet named "Master". Save the workbook as a macro-enabled file. Since your files are in different folders, you will be prompted to choose a folder and a file to open.
Code:
Sub CopySheet()
Application.ScreenUpdating = False
Dim flder As FileDialog
Dim FileName As String
Dim FileChosen As Integer
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Set wkbDest = ThisWorkbook
OpenFile:
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.Title = "Please Select an Excel File"
flder.InitialFileName = "c:\"
flder.InitialView = msoFileDialogViewSmallIcons
flder.Filters.Clear
flder.Filters.Add "Excel Files", "*.xls*"
MsgBox ("Select a folder and then a file to open.")
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set wkbSource = Workbooks.Open(FileName)
wkbSource.Sheets("Sheet1").UsedRange.Copy
wkbDest.Sheets("Master").Cells(wkbDest.Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
wkbSource.Close savechanges:=False
If MsgBox("Do you want to open another workbook?", vbYesNo) = vbYes Then GoTo OpenFile
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 copy data in VBA from different named workbook each time |
| How to use a macro to pull every Nth row of data |
| How to move monthly data into columns to rows |
| How to compare 2 columns and pull missing data into 3rd column |
| How to copy from multiple workbooks to master file and keep overwrite data |
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.