PDA

View Full Version : Solved: Changing Month In Cell Everytime Sheet Is Copied?



Simon Lloyd
02-11-2007, 04:06 AM
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?


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
Regards,
Simon

Bob Phillips
02-11-2007, 05:01 AM
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

Simon Lloyd
02-11-2007, 09:39 AM
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
DateSerialbut was unsure how to manipulate it!

Right its shower time now and off to work!

Talk soon,

Regards,
Simon