New Sheet Name

[Michael168]

Please kindly help me to modify the macro below

Sub NewSheet()

Sheets("Master").Visible = True
Sheets("Master").Select
Sheets("Master").Copy After:=Sheets(98)
Sheets("Master").Select
Range("B3:N59").Select
Selection.Copy
Sheets("Master (2)").Select
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=1
Sheets("Master").Select
Sheets("Master").Visible = False
Sheets("Master (2)").Select
Range("M11").Select

End Sub

How can I change the above macro so that the new sheet will always b
at the last sheet and name after the previous last sheet plus 1 like i
the previous last sheet name is "F0098" then the newsheet name will b
"F0099" and so on when I run the macro every time.

Thanks
Michae

 

[Tom Ogilvy]

Sub NewSheet()
Dim sh as Worksheet
Sheets("Master").Visible = True
Sheets("Master").Select
Sheets("Master").Copy After:=Worksheets(worksheets.count)
set sh = Activesheet
sName = sh.previous.Name
' as an example, adjust to suit your naming scheme
sh.name = left(sName,len(sName)-4) & _
format(clng(Right(sName,4)),"000")
Sheets("Master").Select
Range("B3:N59").Select
Selection.Copy
sh.Select
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=1
Sheets("Master").Select
Sheets("Master").Visible = False
sh.Select
Range("M11").Select
End Sub

Assumes the current last worksheet has this numerical type name.

since you are copying master, why go back and copy data from master. Did
you want to change that to copy data from the previous sheet?