Posts by Gretel

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