CODEWITHSUNDEEP
vbaexcel
user5219796
user5219796

Reputation:

Create number of worksheets and rename them

I created the following code to create a number of worksheets based on the input and rename them. When opening the document there will be two worsheets called "General" and "P-1" where "P-1" serves as the template.

The problem I have is that whenever I start the macro and enter a number I will get an error telling me that the name is already assigned.

Sub KlickME()
Dim Rng As Range
Dim WorkRng As Range
Dim newName As String
Dim convert As Integer
Dim i As Integer
Dim b As Integer


newName = "P-"


howMany = Application.InputBox("Many", "How Many", "", Type:=2)
convert = CInt(howMany - 1)



For i = 1 To convert
    Sheets("P-1").Copy After:=Sheets("P-1")

    For b = 1 To Application.Sheets.Count
        Application.Sheets(b).name = newName & b
    Next b
Next i


End Sub

Whenever I run the macro without the "General" worksheet and only the "P-1" worksheet present, it works without any problems.

Upvotes: 0

Views: 67

Answers (2)

gizlmo
gizlmo

Reputation: 1922

I took your new code and made it a little shorter:

Sub KlickME()

Dim howmany As Integer
Dim i As Integer

howmany = Application.InputBox("Many", "How Many", "", Type:=2)

For i = 1 To howmany

    Sheets("P-1").Copy After:=Sheets(Sheets.Count) 'Inserts sheet on last position

    Sheets(Sheets.Count).Name = "P-" & i + 1 'renames sheet on last position
Next i
End Sub

Upvotes: 1

user5219796
user5219796

Reputation:

Ok so for everyone interested, I solved the issue. Maybe a bit unconventional but it does what it is supposed to do. Please see the code below:

Sub KlickME()
Dim Rng As Range
Dim WorkRng As Range
Dim newName As String
Dim convert As Integer
Dim i As Integer
Dim b As Integer

newName = "P-"


howMany = Application.InputBox("Many", "How Many", "", Type:=2)
convert = CInt(howMany - 1)



For i = 1 To convert
    Sheets("P-1").Copy After:=Sheets("P-1")
Next i

For b = 2 To Application.Sheets.Count
        Set Sheet = ActiveWorkbook.Worksheets(b)
        If Sheet.name <> "P-1" Then
            Application.Sheets(b).name = newName & b - 1
        End If
Next b

End Sub

Upvotes: 0

Related Questions