Copying data from multiple sheets using VBA

[mrnassaro]

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:

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

 

[mrnassaro]

That error box is generated by the code. The file "C:\Users\NassarM\Desktop\Roll Up.xlsm" must exist. You can remove the "on error goto errorhandler" line of code to find out what line of code is crashing. Dave

Thanks Dave! I got the error because i did not have the "Roll UP" file in my desktop. it works now, thank you so much

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave

 

[mrnassaro]

You are welcome. Thanks for posting your outcome. Dave

Thanks Dave. Would it be possible to ask another question here?

once I have copied all the sheets, I want to merge them together in one sheet as a summary while keeping them in separate sheets as well as a reference. is there a line of code that I can add to perform this operation?

 

[NdNoviceHlp]

Merge them together in 1 sheet.. how? Add a summary sheet and then add all A1's, A2's, A3's etc. from all the sheets together? I'm guessing it wouldn't be that simplistic and it would involve plenty of code lines and the knowledge of what's actually on the sheets. It would be best for you to determine what exactly U want in a summary sheet, make some dummy data sheets and then post the wb along with a new thread if you have any difficulties. Good luck. Dave

 

[mrnassaro]

Merge them together in 1 sheet.. how? Add a summary sheet and then add all A1's, A2's, A3's etc. from all the sheets together? I'm guessing it wouldn't be that simplistic and it would involve plenty of code lines and the knowledge of what's actually on the sheets. It would be best for you to determine what exactly U want in a summary sheet, make some dummy data sheets and then post the wb along with a new thread if you have any difficulties. Good luck. Dave

thank you so much! Will do that