Hello Experts!
I am working with the following VBA is works great for me. however, I am trying to do the following but unable to:
1- when I run this VBA for the first time it works. I want this to be running into new sheet that I could save, because I want this to be a master file that can be updated every time. and if possible to run the file directly when it is opened? ( I am trying to make this file as easy to non excel people)
2- can I only copy the unhidden sheets?
VBA Code:
I am working with the following VBA is works great for me. however, I am trying to do the following but unable to:
1- when I run this VBA for the first time it works. I want this to be running into new sheet that I could save, because I want this to be a master file that can be updated every time. and if possible to run the file directly when it is opened? ( I am trying to make this file as easy to non excel people)
2- can I only copy the unhidden sheets?
VBA Code:
VBA Code:
Sub mergeFiles()
'Merges all files in a folder to a main file.
'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
sourceWorkbook.Close
Next i
End Sub
Last edited by a moderator: