VBA code to name the worksheet with quarter end date automatically

[hyunee]

VBA code to name the worksheet with quarter end date automatically
Hello everyone

i am new to VBA codings.. can anyone please help?? i have been trying to figure this out on my own for the last two days and it's not going anywhere. Can anyone correct my codes or suggest better codes for this?

I am trying to figure out codes to duplicate an existing worksheet with the worksheet name " XYZ report Q2 2016" and i want this sheet to be updated for Q3 with the worksheet name "XYZ report Q3 2016"

Below codes are what i have done so far and they are not working.
1. Sheet(2) Cell C4 has the last quarter date 30 June 2016
2. Sheet(1) is the report that i want to duplicate and that i wanted it to be updated for Q3




Sub Duplicatereport()
'
' Duplicatereport Macro
'
Dim mydate As Date
Dim mydate2 As Date
mydate = Sheets(2).Range("c4")
mydate2 = DateSerial(Year(mydate), Month(mydate) + 3, Day(mydate))


Sheets("XYZ Q2 2016").Copy after:=Sheets(2)
ActiveSheet.Name = Format(mydate2, "mm yyyy")

End Sub

[YasserKhalil]

Hi hyunee
Welcome to the forum..
Please put the codes between code tags

Try this code

Please Login or Register  to view this content.

[hyunee]

Thank you!

Now i realised that i still have to keep updating manually the cell c4. is there anyway i can make the cell to be updated automatically to next qtr end date?

For example, next December quarter, i will have to update C4 to 30 September 16 for my VBA codes "mydate2" to be updated to 30 December 16

can you please suggest a way to do that if any?

Thank you.

[YasserKhalil]

You can add this line at the end of the code to update Cell C4

Please Login or Register  to view this content.