Solved: Changing Month In Cell Everytime Sheet Is Copied?

[Simon Lloyd]

Hi all, i have supplied a solution to a question asking for some code to copy sheet 1 10 times, the code is posted below.....however the ops has asked for the value of A6 which is a month to be advanced in A6 on each copied sheet!

I have tried DataSeries, xlSeries, xlMonth, etc but to no avail, any ideas?

[VBA]
Sub Macro1()
Dim i As Integer
Dim Sc, R As Variant
R = Sheets("Sheet1").Range("A6").Value
For i = 1 To 10
With Sheets("Sheet1")
Sc = Sheets.Count
.Copy After:=Sheets(Sc)
End With
With ActiveSheet
Range("A6") = xlMonth + 1''''''have no idea here on how to advance month on each copied sheet!
End With
Next i
End Sub
[/VBA]Regards,
Simon

[Bob Phillips]

[VBA]

Sub Macro1()
Dim i As Long
Dim nSheets As Long, R As Variant
Dim upDate As Date
upDate = DateValue("01-" & Sheets("Sheet1").Range("A6").Value & "-2007")
nSheets = ActiveWorkbook.Worksheets.Count
For i = 1 To 10
Worksheets("Sheet1").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Range("A6").Value = Format(upDate, "mmmm")
upDate = DateSerial(2007, Month(upDate) + 1, 1)
Next i
End Sub
[/VBA]

[Simon Lloyd]

Thanks Bob, i had already tried this line using R instead of upDate

ActiveSheet.Range("A6").Value = Format(upDate, "mmmm")

and i had looked at this

DateSerial

but was unsure how to manipulate it!

Right its shower time now and off to work!

Talk soon,

Regards,
Simon