Macro to generate sequentially named sheets

N

Neil

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
 
E

Earl Kiosterud

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

Neil

Thanks Earl,

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

Regards,

Neil
 
Top