Macro to import multiple sheets as Tabs, Freeze the top Row and apply a filter to each sheet

Klemvore

New Member
Joined
Apr 25, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I am trying to modify a macro, splice another macro into it, and sort out a 3rd.

The first one is this.. which works
But I don't want the workbooks imported as Sheet 1 Sheet 2 Sheet 3
I want them imported to have the names of the files they were imported from
VBA Code:
Sub ImportSheetsAsTabs()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook
 
    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
 
    If (vbBoolean <> VarType(fnameList)) Then
 
        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0
 
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
 
            Set wbkCurBook = ActiveWorkbook
 
            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1
 
                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
 
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
 
                wbkSrcBook.Close SaveChanges:=False
 
            Next
 
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
 
            MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If
 
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

I want it as it imports them to Freeze the top Row Like this
VBA Code:
Sub FREEZE()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
Next ws
End Sub

And also to apply a filter
This I seem to have sorted out.. I added 2 lines to the freeze command above and now it does both
VBA Code:
Sub FREEZE()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Cells.Select
    Selection.AutoFilter
Next ws
End Sub

So how do I get those sheets to retain the names of the files on the tabs... and what is the best way to splice that freeze and filter macro into the first file import as sheets macro?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Actually seems like I got it but there is a short character limit... anyone know how to make it allow more?

VBA Code:
Sub Complete()

Dim FolderPath As String
Dim File As String
Dim i As Long

Application.ScreenUpdating = False

FolderPath = "C:\Regional Report\"

File = Dir(FolderPath)

Do While File <> ""

    Workbooks.Open FolderPath & File
        ActiveWorkbook.Worksheets(1).Copy _
            after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
            ActiveSheet.Name = Replace(File, ".xlsx", "")
            Workbooks(File).Close

    File = Dir()

Loop

Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Cells.Select
    Selection.AutoFilter
Next ws

Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top