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

 

[defender]

Hi fluff, wonder if you can help me with another problem. I have a workbook whereby i want to copy a worksheet and put it into then next worksheet (the worksheet contains a formula so named worksheet has to be sequential i.e sheet1, sheet2, sheet3 etc for the formula to work in the next worksheet, if naming by date its causing the formula not to work because the dates are not sequential) However i want the worksheets to be dated sequentially

are you able to combine these two macros for me? and would that then work solve the problem of the formula which is reading MAX(IF(LEFT(INDIRECT("Sheet" the previous sheet.

Sub MakeCopiesofSheet()
Dim wsSrc As Worksheet
Set wsSrc = Sheets("Sheet5")
Dim x As Long
With wsSrc
x = .Range("N1").Value
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet" & "6"
.Range("N1").Value = 1
End With
End Sub

and the second macro is the one you created for me:

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


Thanks

 

[Fluff]

Not sure if I can help on this, as formulae are not my strong point, but looking at your code, what is the value of x, as you don't seem to be using it?

 

[defender]

I think i managed to solve it, thanks

 

[Fluff]

Glad you got it working

 

[defender]

Hi fluff, sorry to bother you again. It seems like the macro works to a certain extent. This is what i am using now:

Sub CreateSheets()


Dim Cnt As Long
Dim i As Long
Dim StDy As Date
Dim EnDy As Date

StDy = "01/01/2018"
EnDy = "06/01/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
ActiveSheet.Range("N1").Value = Format(Cnt, "dd-mm-yyyy") & " Page" & i - 1
Next Cnt
End Sub



I was just testing it over 6 days to begin with.

Day 1 up to Day 3 works fine, and my formula which is supposed to increment over worksheets works as i am using N1 as a reference to the previous worksheet. However when i get up to day 4 i'm getting #REF in my formula column and i cannot understand why as the other sheets have been copying over ok. So anything after day 4 copies the worksheet but #REF in my formula cells. Any idea why this might be happening?

 

[Fluff]

What is your formula?

 

[defender]

=IF(D8="","","PSCXXXX-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-"&TEXT(COUNTIF(N$7:N7,"PSCXXXX-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-*")+MAX(IF(LEFT(INDIRECT("Sheet" & LOOKUP(99^99,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N7:N50"),10)="PSCXXXX-"&VLOOKUP(D8,Names!$A$1:$C$39,2,FALSE)&"-",--RIGHT(INDIRECT("Sheet" & LOOKUP(999^999,--RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255),ROW($A$1:$A$3))) -1 & "!N7:N50"),4)))+1,"0000"))

 

[Fluff]

Haven't got a clue. That formula is beyond my ken.
I'd suggest starting a new thread

 

[defender]

no problem. Maybe you could help me with this macro:

Sub MakeCopiesofSheet()
Dim wsSrc As Worksheet
Set wsSrc = Sheets("Sheet2")
Dim x As Long
With wsSrc
x = .Range("N1").Value
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet" & "3"
.Range("N1").Value = 1
End With
End Sub
When i run this macro it copies worksheet 2 and pastes it into worksheet 3. If i want to do the same thing but now into worksheet 4 then i have to change 3 to 4 ("sheet" & "4") and re-run the macro. Is it possible when i run the macro first time it will do sheet 3, then i run again it will do sheet 4, and so each time i run the macro it copies sheet 2 into the next sheet number without having to manually change the number in the coding? And can this be done for an infinite number?

 

[Fluff]

What is the value of x