Activate Window Based on Cell Value

DominicPAH

New Member
Joined
Oct 10, 2017
Messages
8
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

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Try this approach:
1) add this "set" in this position:
VBA Code:
        Workbooks.Open Filename:=File
    Set dWb = ActiveWorkbook                         '<<< ADD THIS LINE HERE
    Application.ScreenUpdating = True

Then replace your Windows("12-04-2021.xlsx").Activate with
VBA Code:
dWb.Activate

Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,724
Members
417,108
Latest member
Thein Than

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
Top