Macro Help required

[Paul Sheppard]

I have 40+ Workbooks (likely to grow to 100+ in the new year) where I am
going to track sales, with a worksheet for each month, ongoing.

To make the process faster each month I would like a macro that copies
the previous months sheet, clears all entries (these parts I can do)
now for the hard part the 2 bits I need help with:

1. I need it to automatically rename the worksheet to be 1 after the
one it copies, so if it copies the sheet at the right hand end which is
Dec 05 it renames it Jan 06 etc

2. On the Dec 05 sheet cell E2 =Nov 05!E17, I need this to
automatically update to read =Dec 05!E17 on the newly created Jan 06
sheet

Is this possible?

 

[Bob Phillips]

1. Try

Format(DateSerial(Year(DateValue("01 " & Activesheet.Name)),
Month(DateValue("01 " & Activesheet.Name)) + 1, 1), "mmm yy")

2. Similarly, try

Format(DateSerial(Year(DateValue("01 " & Left(Range("E2",6)))),
Month(DateValue("01 " & Left(Range("E2",6)))) + 1, 1), "mmm yy") & "!E17"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...

 

[Paul Sheppard]

Thanks Bob

1. Try

Format(DateSerial(Year(DateValue("01 " & Activesheet.Name)),
Month(DateValue("01 " & Activesheet.Name)) + 1, 1), "mmm yy")

2. Similarly, try

Format(DateSerial(Year(DateValue("01 " & Left(Range("E2",6)))),
Month(DateValue("01 " & Left(Range("E2",6)))) + 1, 1), "mmm yy")
"!E17"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Sheppard
<[email protected]>
wrote in message

http://www.excelforum.com/showthread.php?threadid=491780