Activating a Workbook with a changing name

[JFlagg]

Hi,

I'm new to VBA, I'm recording a macro to format a specific file that a team uses to work through a que, I'm trying to get my macro to reference the workbook regardless of the changing number in the file. This name change is caused by the way the file downloads in certain browsers i.e chrome and edge vs IE, currently it looks like this:

Windows("Recalc.xls").Activate
The various names that it could need to reference would be:
Windows("Recalc (1).xls").Activate
Windows("Recalc (2).xls").Activate....

The sheet name also changed per the workbook name and I'd have to reference that changing sheet name at some point too. I've paste part of what the recorder generated below.

Windows("Recalc.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("A2").Select
ActiveWorkbook.Worksheets("Recalc").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Recalc").Sort.SortFields.Add(Range("F3:F299"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
, 0)
ActiveWorkbook.Worksheets("Recalc").Sort.SortFields.Add(Range("F3:F299"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
255, 0)

Also this macro is split into several subs for some reason, the person who originally recorded it left the company though. I'm wondering if there is a way to set a variable for the name that could change in each sub.

 

[steve the fish]

Hi. Im presuming the sheet is named by the same name as the workbook. The following shows how you could get both the workbook and the worksheet into two variables, namely wb and ws.

Dim wb As Workbook, wrkbk As Workbook, sh As Worksheet

For Each wrkbk In Workbooks
    If wrkbk.Name Like "Recalc*" Then
        Set wb = wrkbk
        Set sh = wb.Sheets(Split(wrkbk.Name, ".")(0))
    End If
Next