automate editing a macro

S

stevejones42

New Member
Joined
Jun 7, 2022
Messages
4
Office Version
  1. 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

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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Welcome to the Board!

Is the date always a Saturday date?
When you do you typically run this macro?

What is the relationship between the date in the file names, and the date you run this macro on?
Are you running it for the prior week, or the upcoming week?
 
Upvote 0
There is no relationship between the date and when the macro is run.
Dates are usually a Saturday, unless it is a year end date.
The date on the spreadsheet is purely for identification, as the data in the spreadsheet is updated on a weekly basis
 
Upvote 0
stevejones42 said:
There is no relationship between the date and when the macro is run.
Click to expand...
So how can we tell what the name of the new report should be?
Are the files are in a certain folder?
Of so, does that folder only show the most recent files, or all old files to?
 
Upvote 0
The name of the report will always be Report_ followed by the next weekending date.
I was thinking of a type of find and replace script
 
Upvote 0
OK, if it is to always be the next Saturday, then you could do a VBA calculation like this to store the date string as a variable:
VBA Code: 
    Dim d As String
    d = Format(Date + 7 - Weekday(Date, vbSunday), "yymmdd")
and then you could refer to your files like this:
VBA Code: 
    Windows("Report_" & d & ".xlsm").Activate
 
Upvote 0
Solution
Hi Thank you for your assistance.
I ended up creating the valuable and then using an input box to populate the variable.
 
Upvote 0
You are welcome.

You could skip the variable and do it all in one step, but sometimes that gets to be a bit confusing when you try to do too much at once.
So I think it is often easier to see/follow by breaking it up like that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
105
willow1985
willow1985
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
345
Joe4
Joe4

Forum statistics

Threads
1,215,020
Messages
6,122,712
Members
449,093
Latest member
Mnur

Share this page

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
Back
Top