VBA Code to run the code in new workbook?

[mrnassaro]

I have a code that I want to run when I click on the button, but I want the results go into completely new sheet that the user can save. is there a line of code to help?

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
If sourceWorkbook.Sheets(tempWorkSheet.Name).Visible = True Then
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
End If
Next tempWorkSheet
     
        'Close the source workbook
        sourceWorkbook.Close
    Next i
    End Sub

 

[rlv01]

...but I want the results go into completely new sheet that the user can save.

I'm assuming that by "new sheet" you mean "new workbook"

'Assign this macro to your button
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, ResultsWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
   
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set ResultsWorkbook = Application.Workbooks.Add
   
    '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
        Set sourceWorkbook = Workbooks.Open(Filename:=tempFileDialog.SelectedItems(i))
       
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            If tempWorkSheet.Visible = True Then
                With ResultsWorkbook
                    tempWorkSheet.Copy after:=.Sheets(.Sheets.Count)
                End With
            End If
        Next tempWorkSheet
       
        'Close the source workbook
        sourceWorkbook.Close SaveChanges:=False
    Next i
   
    ResultsWorkbook.Close SaveChanges:=True
End Sub

 

[Norie]

You could try something like this.

It creates a new workbook by copying the very first sheet to be copied without specifying a destination, it then copies every subsequent worksheet being copied to that new workbook.

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 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
           
            If sourceWorkbook.Sheets(tempWorkSheet.Name).Visible = True Then

                ' if it's the first worksheet to be copied copy it to a new workbook and set that workbook as mainWorkbook
                ' else copy it to mainWorkbook
                If mainWorkbook Is Nothing Then
                    tempWorkSheet.Copy
                    Set mainWorkbook = ActiveWorkbook
                Else
                    tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
                End If
            End If
        Next tempWorkSheet

        'Close the source workbook
        sourceWorkbook.Close
    Next i
   
End Sub

 

[mrnassaro]

it worked !! I can't thank you enough!