Copying data from multiple sheets using VBA

mrnassaro

New Member
Joined
Jan 23, 2015
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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:

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:

mrnassaro

New Member
Joined
Jan 23, 2015
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mrnassaro

New Member
Joined
Jan 23, 2015
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,846
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

New Member
Joined
Jan 23, 2015
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,670
Messages
5,725,677
Members
422,635
Latest member
crisis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top