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
I want it as it imports them to Freeze the top Row Like this
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
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?
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?