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

 

[Joe4]

If you want to apply that code to all worksheets in the workbook, you can do that like this:

Sub filter_and_freeze_top_row()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Rows("1:1").AutoFilter
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
    Next ws
End Sub

 

[heathball]

If you want to apply that code to all worksheets in the workbook, you can do that like this:

Sub filter_and_freeze_top_row()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Rows("1:1").AutoFilter
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
    Next ws
End Sub

thank you!
I can apply that seperately, which is great.

Is there a way to add the deleting of the 3 sheets, into the main merge code?

 

[Joe4]

I don't see why not. Your code is pretty simple. You should just be able to add it into your code.
Are you having some sort of issue with it?

 

[heathball]

I don't see why not. Your code is pretty simple. You should just be able to add it into your code.
Are you having some sort of issue with it?

I am not sure exactly where it should go. I assume it needs to be at the start of the code. But there may be a step i am missing.

its the 'active workbook.' part that seems not to work so far

Edit - it would not go at the start of the code, as all sheets cannot be deleted, so its a question of where it goes, and also how.

 

[heathball]

I don't see why not. Your code is pretty simple. You should just be able to add it into your code.
Are you having some sort of issue with it?

the debug is yellow here
ActiveWorkbook.Sheets("Sheet1").Delete

 

[Joe4]

Why don't you try it and see how it works (that is often the best way to learn)?
If you cannot figure it out, post the code you tried, and we can help you fix things up.

 

[Joe4]

the debug is yellow here
ActiveWorkbook.Sheets("Sheet1").Delete

I don't see that line of code in anything you have posted so far.

What exactly is the error message you getting?
Can you post your entire procedure?

 

[heathball]

I don't see that line of code in anything you have posted so far.

What exactly is the error message you getting?
Can you post your entire procedure?

Thanks Joe,

The debugger is highlighting....ActiveWorkbook.Sheets("Sheet1").Delete

the only error message was checking the debugger.

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

    Next i
 
End Sub

 

[Joe4]

Did you mean to put the sheet deletion rows AFTER you have closed the Workbook (look at the line of code just above it)?
If you want to delete these three sheets in each workbook, logically speaking, that code would need to occur BEFORE you close each workbook.