help with macro to sequential dates across worksheets

defender

New Member
Joined
Dec 24, 2017
Messages
41
Hi All,

I need to a macro that will sequential date my worksheets with text and then a sequential number at the end.

i.e 06/01/18Page1, 07/01/18page2, 08/01/18Page3, 09/01/18Page4........

I probably be need to to end of year

Also, at the same time i need sheet2 from my workbook to be copied across to all the dates.

Can anyone help?

Thanks.

P.S VB novice here:rolleyes:
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Not sure if you want 1 sheet per day, or 1 per month.
This will do 1 per month
Code:
Sub CreateSheets()

   Dim Cnt As Long
   Dim Mnth As Date
   
   For Cnt = 1 To 12
      Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
      Mnth = "1/" & Cnt & "/" & Year(Date)
      ActiveSheet.Name = Format(Mnth, "mm-dd-yyyy") & " Page" & Cnt
   Next Cnt
End Sub
 

defender

New Member
Joined
Dec 24, 2017
Messages
41
Hi Fluff, thanks that works nicely. If i want to pick a start date and a date to end on how would i do that?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub CreateSheets()

   Dim Cnt As Long
   Dim Mnth As Date
   Dim StMnth As Long
   Dim EnMnth As Long
   
   StMnth = InputBox("Please enter the first month (like 1 for Jan)")
   EnMnth = InputBox("Please enter the last month (like 12 for Dec)")
   
   For Cnt = StMnth To EnMnth
      Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
      Mnth = "1/" & Cnt & "/" & Year(Date)
      ActiveSheet.Name = Format(Mnth, "mm-dd-yyyy") & " Page" & Cnt
   Next Cnt
End Sub
 

defender

New Member
Joined
Dec 24, 2017
Messages
41

ADVERTISEMENT

Hi fluff, i ran the macro, with the intention of dates running from january to end of December. But i only got the macro to produce 01/01/18Page1 to 12/01/18Page2 :-/

Sub CreateSheets()

Dim Cnt As Long
Dim Mnth As Date
Dim StMnth As Long
Dim EnMnth As Long

StMnth = "1"
EnMnth = "12"

For Cnt = StMnth To EnMnth
Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
Mnth = "1/" & Cnt & "/" & Year(Date)
ActiveSheet.Name = Format(Mnth, "mm-dd-yyyy") & " Page" & Cnt
Next Cnt
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Dou you want 1 sheet per month, or 1 sheet per day?
 

defender

New Member
Joined
Dec 24, 2017
Messages
41

ADVERTISEMENT

1 sheet per day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub CreateSheets()

   Dim Cnt As Long
   Dim i As Long
   Dim StDy As Date
   Dim EnDy As Date
   
   StDy = "01/01/2018"
   EnDy = "01/02/2018"
   
   For Cnt = StDy To EnDy
      i = i + 1
      Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
      ActiveSheet.Name = Format(Cnt, "dd-mm-yyyy") & " Page" & i
   Next Cnt
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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