CODEWITHSUNDEEP
excelvba
Ollie_Australia
Ollie_Australia

Reputation: 3

Troubleshooting VBA - Merge only specific, multi worksheets from a selected folder

I have two issues with this code:

  1. I want to copy a specific sheet, rather than all sheets in the workbooks
  2. Some of the source workbooks have a macro in them, this throws a 'Excel Security Notice' which interrupts the execution of the code and all worksheets don't copy over

Issue 1 target behaviour: I want to select files from a folder and have a specific worksheet copied over into my reporting template. This worksheet is always in tab position 1 and always contains "current and closed".

With the below code I have found online, it functions, but brings all worksheets from the source workbooks, including hidden sheets. I only want either the first tab, or the tab containing "current and closed" to be copied over.

Issue 2 target behaviour: I want the code to ignore/evade the security notice so that the migration of source worksheets that have a macro in them is uninterrupted. I don't mind if the macro in the source workbooks is disabled as they aren't required in the consolidated reporting workbook

Thanks for your collective brain power!

Sub mergeFiles() 'Merges all files in a folder to a main file. 'https://professor-excel.com/merge-excel-files-combine-workbooks-one-file/#Method_4_Merge_files_with_a_simple_VBA_macro

'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

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 without saving changes in the source workbooks
sourceWorkbook.Close savechanges:=False

Next i

End Sub

Upvotes: 0

Views: 210

Answers (1)

Oliver Leung
Oliver Leung

Reputation: 792

To suppress warnings, turn off alerts, open the workbook as ReadOnly and don't update the link:

        Application.DisplayAlerts = False
        Workbooks.Open tempFileDialog.SelectedItems(i), False, True

To act only on a particular worksheet, add conditional check within the For...next loop based on your priority whether "current and closed" is higher priority than the 1st tab, or you want to copy both. To check the worksheet name, use tempWorkSheet.Name and Instr() function to check whether it contains "current and closed".

Remember to re-enable alerts at the end of your function by:

Application.DisplayAlerts = True

Upvotes: 0

Related Questions