Mass change of modules

Mr Marvin

New Member
Joined
Sep 8, 2021
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
Hi

I am fairly new to this vba/macro I need little help. I have a workbook that has a module for each day of the month that links to reports for that month e.g August. Is there a quick way where you can replace the month in each module without having to go into it and amend manually. Something similar to what find and replace would do in a worksheet.
Tia
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome to the Board!

Is the code in each module all doing the same thing, just for different months?
If so, then you should be able to only have one module that can be applied dynamically for all 12 months.
You probably just need some updates to your code so that it works dynamically like that.
We would need to see the code in order to help you do that.
(Please be sure to use Code Tags when posting, see: How to Post Your VBA Code).
 
Upvote 0
hi @Joe4 thanks for the reply. this is the code below i use. basically i have a template which creates pivottables which then feed into the workbook which contains worksheets for each day of the month. the code below will be for the 10th september.

Sub Copy_Method10()

'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PivotTable").Range("B2:E30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("M2")

'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PivotTable").Range("B2:E30").Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("M2").PasteSpecial Paste:=xlPasteValues

'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("CondAccPivTab").Range("B2:C30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("R2")

'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("CondAccPivTab").Range("B2:E30").Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("R2").PasteSpecial Paste:=xlPasteValues

'Copy range to another workbook
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PendingPivotTable").Range("B1:E30").Copy _
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("U2")

'Copy range to clipboard
Workbooks("Prod log 2021-2022.xlsm").Worksheets("PendingPivotTable").Range("B1:C1000").Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Daily Figs September.xlsm").Worksheets("10th").Range("U2").PasteSpecial Paste:=xlPasteValues

End Sub
 
Upvote 0
Note that you can make the things that change, like workbook and worksheet names, dynamic, so you don't need multiple copies of the code.

For example, your file name of "Daily Figs September.xlsm". You can build that dynamically based on the current month, like this:
VBA Code:
    Dim fname As String
    fname = "Daily Figs " & Format(Date, "mmmm") & ".xlsm"
    MsgBox fname
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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