Copy data from Multiple workbooks into One

When you say that the files are in different locations, does that mean they are in different folders? Do you want to copy data from all the sheets in the source workbooks or from just one sheet? Do you want to copy the entire sheet or only part of it? Where in the Master do you want to paste the data, all in one sheet or in separate sheets?

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.

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

You are very welcome.

Quote from Mumps

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

Display More

Hi Mumps,

I could really use your help. This macro does exactly what I need it to do, but I could use some help tweaking it so that it processes all files within the folder without me having to select each file and leaves out the first line as they are headers. Is this possible?

I fully realise that I'm piggy backing off your hard work, but it's a gem of a macro and will save me a lot of time.

Many thanks,
Gretel

Gretel: Welcome to the Forum. According to Forum rules, you should not post your question in another person's thread. Please start a new thread explaining exactly what you want to do. Also, it would make it easier to help if you attach a copy of your file. Once you have started a new thread, send me a private message with a link to the thread and I will be glad to have a look at it. Include a link to this thread if you feel it is useful.