stevejones42
New Member
- Joined
- Jun 7, 2022
- Messages
- 4
- Office Version
- 365
I have a macro that activates a separate worksheet. The worksheet changes it's name very week. Report_220521 will be Report_220528 the following week.
I want to automate updating each occurence of Report_220521 to whatever the new report is ie Report_220528 without having to go through it line by line very week.
Is that possible?
The macro is below.
Any assistance would be most welcome.
Thanks
I want to automate updating each occurence of Report_220521 to whatever the new report is ie Report_220528 without having to go through it line by line very week.
Is that possible?
The macro is below.
Any assistance would be most welcome.
Thanks
VBA Code:
Windows("Report_220521.xlsm").Activate
Sheets("Mitcham").Select
ActiveWindow.SmallScroll Down:=-27
Range("G18:S18").Select
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
Range("G23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=27
Range("G50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("US").Select
ActiveWindow.SmallScroll Down:=-9
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-24
Range("G22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=18
Range("G49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("Czech").Select
ActiveWindow.SmallScroll Down:=-21
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-30
Range("G24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=27
Range("G51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("India").Select
ActiveWindow.SmallScroll Down:=-24
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-27
Range("G25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=18
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=30
Range("G52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("China").Select
ActiveWindow.SmallScroll Down:=-21
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-24
Range("G26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=15
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=18
Range("G53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("Jacottet").Select
ActiveWindow.SmallScroll Down:=-30
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-21
Range("G28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=9
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=21
Range("G55").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
Sheets("Bruntons").Select
ActiveWindow.SmallScroll Down:=-24
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-24
Range("G27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=30
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
Range("G54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=18
Sheets("COHL").Select
ActiveWindow.SmallScroll Down:=-15
Range("G18:S18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=-24
Range("G29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Windows("Report_220521.xlsm").Activate
ActiveWindow.SmallScroll Down:=18
Range("G45:S45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("220521_HO macro.xlsm").Activate
ActiveWindow.SmallScroll Down:=21
Range("G56").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
End Sub