Macro to generate sequentially named sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil - 02 Sep 2005 22:53 GMT

Hi All,

I have a workbook containing a 'fixed' sheet titled 'Lists' containing my
dynamic ranged lookup tables and another sheet titled 'Sheet A'.
'Sheet A' accepts input from my users and can be the first of many sheets
sequentially named, ie, 'Sheet B', 'Sheet C' etc.
Due to restrictions elsewhere, my sheets cannot be named numerically
sequentially.

I would like to generate a macro that can identify the latest instance of my
sequential sheets, copy that sheet into the same workbook and index the
name, ie. copy 'Sheet F' to 'Sheet G'.

Is this possible ?

There is also a possibility that I will want to select a sheet to copy, not
just the last in my current sequential group of sheets, I'd like to be able
to select the sheet then have it copied by macro, again the new sheet being
allocated a name at the end of my sequence, ie. copy 'Sheet B' to 'Sheet H'.

Thanks for the group's assistance.

Neil

Reply to this Message

Earl Kiosterud - 03 Sep 2005 01:39 GMT

Neil,

Here are a couple of procs.

Sub CopyLastSheet()
Dim OldName As String, NewName As String
Dim LastSheet As Worksheet
Set LastSheet = Sheets(Sheets.Count) ' last physical sheet
OldName = LastSheet.Name
NewName = Left(OldName, Len(OldName) - 1) & Chr(Asc(Right(OldName, 1)) + 1)
LastSheet.Copy After:=LastSheet
ActiveSheet.Name = NewName
End Sub

Sub CopyActiveSheet()
Dim OldName As String, NewName As String
OldName = Sheets(Sheets.Count).Name ' get name of last physical sheet
NewName = Left(OldName, Len(OldName) - 1) & Chr(Asc(Right(OldName, 1)) + 1)
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = NewName
End Sub

Not thoroughly tested. These aren't too robust. They'll get weird after
Sheet Z, for example.
--
Earl Kiosterud
www.smokeylake.com

> Hi All,
>
[quoted text clipped - 20 lines]
>
> Neil

Reply to this Message

Neil - 03 Sep 2005 09:15 GMT

Thanks Earl,

That works fine, robust enough given my users should only add 3 or 4
sheets....

Regards,

Neil

> Neil,
>
[quoted text clipped - 50 lines]
>>
>> Neil

Reply to this Message