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?


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=491780

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"
wrote in message
news:Paul.Sheppard.1zq06m_1134043802.3354@excelfor um-nospam.com...

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?


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=491780

Thanks Bob

Bob Phillips Wrote:
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"

wrote in message
news:Paul.Sheppard.1zq06m_1134043802.3354@excelfor um-nospam.com...

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?


--
Paul Sheppard



------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread:
http://www.excelforum.com/showthread...hreadid=491780



--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=491780