Add 3 steps to merge VBA code

[heathball]

I am trying to add steps to my main merge VBA code, and I cannot find the solution.


this step to apply to the active workbook... (delete 3 sheets with names)

Sheets("Sheet1").Delete
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete

I need this next part to apply to ALL SHEETS in the active workbook

these 2 steps (add auto filter for row 1, freeze top row)

Rows("1:1").Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

I have tried various things, and none are working.

Any help would be appreciated.


My main code is below

Sub mergeFiles_v669()
    '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

 

[heathball]

I did notice that, and, before it
Set sourceWorkbook = ActiveWorkbook

But the active workbook does not close after running the script.
The original source workbooks close.

and i get the same debugging result for this

Sub mergeFiles_v669_deletesheets()
    '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
        
    ActiveWorkbook.Sheets("Sheet1").Delete
    ActiveWorkbook.Sheets("Sheet2").Delete
    ActiveWorkbook.Sheets("Sheet3").Delete
        
        'Close the source workbook
        sourceWorkbook.Close
        
    

    Next i
    
End Sub

 

[heathball]

There is no doubt i am missing several things.

with this...
If you want to delete these three sheets in each workbook, logically speaking, that code would need to occur BEFORE you close each workbook.

The 3 sheets are on the destination workbook.
Not the original source files. They don't have any sheets requiring a delete.

I did notice that, and, before it
Set sourceWorkbook = ActiveWorkbook

But the active workbook does not close after running the script.
The original source workbooks close.

and i get the same debugging result for this

Sub mergeFiles_v669_deletesheets()
    '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
       
    ActiveWorkbook.Sheets("Sheet1").Delete
    ActiveWorkbook.Sheets("Sheet2").Delete
    ActiveWorkbook.Sheets("Sheet3").Delete
       
        'Close the source workbook
        sourceWorkbook.Close
       
   

    Next i
   
End Sub

 

[Joe4]

OK, let's take a step back to make sure we are all on the same page,

In simple outline form, please list all the steps you want this code to perform, in the order you want them performed.
Nothing technical, just an explanation in plain English.

 

[heathball]

OK, let's take a step back to make sure we are all on the same page,

In simple outline form, please list all the steps you want this code to perform, in the order you want them performed.
Nothing technical, just an explanation in plain English.

Joe,
i think you solved the part that was important, which was an efficient way to apply a simple task to multiple sheets.
Which, extended, means this for me...
"i can separate the steps i need, run seperate VBA codes for them, after the merge, and it is all easy, instead of trying to add it to the main code."

So, thanks for your assistance, and it is all good and solved.