Use the same macro on a weekly file which contains date info

[dvward]

Hi! First question here. I don't have much experience with VBA. I have a .csv file sent to me weekly via email. The name of the file is the same each week but contains the date, which changes each week. I save the file as .xlsm and then run a macro for adding info from another file on another sheet, formatting, and running an xlookup. I recorded the steps as a macro and saved it in PERSONAL, but when I try to run the macro the next week, it doesn't work because the sheet name (which comes from the file name, with dates) from the 1st week is imbedded into the macro. Is there a way to change this so the workbook name is ACTIVEWORKBOOK or THISWORKBOOK? I want to be able to use this macro regardless of the name of the workbook or worksheet name. I get a debug error on Windows("Users.xlsm").Activate . This is the first reference to the sheet name. Any help is much appreciated!!

Here is the code :

With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Selection.AutoFilter
ActiveWorkbook.Worksheets("Users for Valerie-2024-01-09-14").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Users for Valerie-2024-01-09-14").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("O2:O604"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Users for Valerie-2024-01-09-14").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("B2:B604"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Users for Valerie-2024-01-09-14").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("O:O").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "Job Code Profile"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Current Profile"
Sheets.Add After:=ActiveSheet
Windows("Sales Job Code List.xlsx").Activate
Sheets("Sheet1").Select
Columns("A:B").Select
Range("A160").Activate
Selection.Copy
Columns("A:B").Select
Range("A112").Activate
Selection.Copy
Windows("Users for Valerie-2024-01-09-14-00-22.xlsx").Activate

 

[John_w]

Change every Worksheets("Users for Valerie-2024-01-09-14") to Worksheets(1) for a start. You can probably delete the last line.

Please use VBA code tags when posting code - the VBA icon in the message editor.