CODEWITHSUNDEEP
excelvba
NodnarB321
NodnarB321

Reputation: 1

Copying and renaming tab VBA

VBA newbie, I am trying to find a way to increase the number of tabs or sheets by copying the last one and then renumbering it. i.e copying tab 150 then relabeling it 151 and so on.

I've figured out a way to copy the sheet with the code below:

Sub CopySheetRename1()
 
Sheets("150").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "LastSheet"
 
End Sub

But i can't get the new sheet to rename itself.

Any help is appreciated

Upvotes: 0

Views: 2666

Answers (2)

David Wooley - AST
David Wooley - AST

Reputation: 375

Try this :

Sub Loadsoftabscreations() 'as many as you want :)
 
Worksheets("Sheet150").Select

For x = 1 To 300 ' or as many new fresh tabs you want

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "YourTab - " & x

Next x
End Sub

You can set the names in the code as you want.

as per my comment, for copying x number of times:

Sub LoadsoftabscreationsC()
Dim ws As Worksheet
Set ws = Worksheets(Worksheets.Count)

For x = 1 To 10 ' or as many as needed

ws.Copy After:=ws
If IsNumeric(ws.Name) Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name

Next x
End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

This perhaps. It's not clear if you have only sheets numbered or others.

Sub CopySheetRename1()

Dim ws As Worksheet

Set ws = Worksheets(Worksheets.Count)
ws.Copy After:=ws
If IsNumeric(ws.Name) Then ActiveSheet.Name = CLng(ws.Name) + 1
 
End Sub

Upvotes: 1

Related Questions