Activate Window Based on Cell Value

[DominicPAH]

Hi all

I have written the following macro and it works fine, does everything its need but only on "Monday". At the end, I have windows("12.04.2021.xlsx").Activate which works fine when the file I am opening is 12.04.2021.xlsx.

The below shows that the file I open to make changes, pull data from etc is dynamic and based a cell value. I need the window("xxx").Activate to also be dynamic

Coding below

Sub Monday()
'
' Monday Macro

'
    Application.ScreenUpdating = False
        Dim File As String
        File = Range("D5").Value
        Dim Path As String
        Path = "C:\Desktop\Daily Actions Reports"
        ChDir Path
        Workbooks.Open Filename:=File
    Application.ScreenUpdating = True
   
        
    Dim LC As Long
    LC = Range("C" & Rows.Count).End(xlUp).Row
   
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],""2"",""1"")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & LC)
    Range(Selection, Selection.End(xlDown)).Select
    Cells.Select
    ActiveWorkbook.Worksheets("All Colleagues").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("All Colleagues").Sort.SortFields.Add2 Key:=Range( _
        "I2:I3500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("All Colleagues").Sort.SortFields.Add2 Key:=Range( _
        "B2:B3500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("All Colleagues").Sort.SortFields.Add2 Key:=Range( _
        "F2:F3500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("All Colleagues").Sort
        .SetRange Range("A1:X1392")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Windows("Accounts per day Test.xlsm").Activate
    Range("D7:E25").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("12-04-2021.xlsx").Activate ' this is bit I need to be dynamic, the cell would be D5 for this button, F5 for another button, H5 etc (Mon to Fri, every other column)'
    ActiveWorkbook.Save
    ActiveWindow.Close
   
    ActiveWindow.SmallScroll Down:=-15
    Application.CutCopyMode = False
    ActiveWorkbook.Save
           
End Sub

 

[Anthony47]

Try this approach:
1) add this "set" in this position:

        Workbooks.Open Filename:=File
    Set dWb = ActiveWorkbook                         '<<< ADD THIS LINE HERE
    Application.ScreenUpdating = True

Then replace your Windows("12-04-2021.xlsx").Activate with

dWb.Activate

Bye