1

I have the below code as part of a larger macro. I would like to change it to automatically select all files in the folder that starts with "Packaginglist" and ends with ".xlsx". So my team dont have (fail) to select the right files each time.

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.count

    'Open each workbook
    Workbooks.Open tempFileDialog.SelectedItems(i)

    Set sourceWorkbook = ActiveWorkbook

    'Copy each worksheet to the end of the main workbook
    For Each tempWorkSheet In sourceWorkbook.Worksheets
        tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.count)
    Next tempWorkSheet

    'Close the source workbook
    sourceWorkbook.Close
Next i

Its a network folder but we can just call it "C:\Packaging-lists\Merged\" for simplicity.

byte me
  • 770
  • 6
  • 13
Gjeep
  • 27
  • 5
  • Does this answer your question? [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) – Pᴇʜ Jan 16 '20 at 15:08

0 Answers0