Add an additional condition and a button to an existing Macro

Also_Confused

New Member
Joined
Jul 29, 2021
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Hi All,

I used the code below, from an old thread, which creates a macro that:

*creates a copy of the "Master" sheet for each day of a single month
*names each tab with a specific day (Aug-01-2021, Aug-02-2021, Aug-03-2021, etc)

If possible, I would like to add the 2 items below to this Macro, but I have no idea how to do this.

#1. Within the "Master" I also have a specific cell, B3, that has the date. I would like to add to the macro, for this cell to match the date on the tab name in each added sheet. All other cells in the Master sheet should remain identical in added sheets.

#2. Then, create a button on the master, that allows for 1 click to complete the macro.

Can #1 and #2 be added to the macro below so there is only 1 macro in the sheet?


**disclaimer.... This is the first time I have ever worked within the developer tab in Excel, and therefore the first time I have ever worked with a Macro.


VBA Code:
Sub CreateMonthlySheets()
'Creates a copy of "Master" sheet for number of days you specify
'It then renames the copies according to the date. e.g. Jan 01 2017
Application.ScreenUpdating = False
Dim Ans As Date
Dim MO As Integer
Dim DA As Integer
Dim YR As Integer
Dim X As Integer
X = 0
On Error GoTo M
MO = InputBox("Enter month number: (1 - 12)")
DA = InputBox("Enter number of days in the month: (28 - 31)")
YR = InputBox("Enter year: (2017)")
Ans = MO & "/" & 1 & "/" & YR
For X = 1 To DA
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(DateAdd("d", X - 1, Ans), "MMM dd yyyy")
Next
Sheets("Master").Activate
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You made an invalid entry."
Application.ScreenUpdating = True
End Sub


Thanks so much in advance for any assistance!!

Also_Confused
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,141,302
Messages
5,705,587
Members
421,400
Latest member
chakam

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