help with macro to sequential dates across worksheets

[defender]

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

 

[Fluff]

Not sure if you want 1 sheet per day, or 1 per month.
This will do 1 per month

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]

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]

How about

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]

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]

Dou you want 1 sheet per month, or 1 sheet per day?

 

[defender]

1 sheet per day

 

[Fluff]

How about

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

 

[defender]

brilliant, thank you so much.

 

[Fluff]

Glad to help & thanks for the feedback