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]...
>
> 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]

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
news:[email protected]...
>
> 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
>