How to Copy data from Multiple workbooks into One

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.