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

N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
183
Northern NY Design
N
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
509
willow1985
willow1985
B
  • Question Question
Needing code help to sort data
Replies
2
Views
185
Bret1
B
willow1985
  • Solved
VBA For workbooks.open, if file doesnt exist goto on error
Replies
3
Views
194
Logit
Logit
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
327
jolivanes
jolivanes

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