Insert Desired count of new worksheets

[jomili]

I've got a macro that i think should be working, but it's not working the way I intend. What I want it to do is ask how many sheets need to be inserted, then take the input and insert that many sheets.

It asks the question, but it only inserts one sheet, and I don't understand why. I've tried DIMming "Count" as Long, as a Range, and as an Integer, with no effect other than an error on "Range". It works the same just doing "DIM Count". I'd appreciate some help on this one.

Thanks,
John

Sub CreateSheets()
Dim Count

    On Error Resume Next

        Application.DisplayAlerts = False

        

            Count = Application.InputBox _
             (Prompt:="How many sheets do you want to insert?", _
             Title:="HOW MANY?", Type:=1 + 8)

             

    On Error GoTo 0

        Application.DisplayAlerts = True



    If IsNumeric(Count) And Count <> 0 Then

        Worksheets.Add After:=Worksheets(Worksheets.Count)

    Else

       Exit Sub

    End If

End Sub

[romperstomper]

You are only issuing 1 worksheets.add command, hence you only get one sheet.

[Andy Pope]

You are not using the information in the Count variable to output more than 1 sheet.

Sub CreateSheets()
Dim Count
Dim lngIndex As Long

    On Error Resume Next

        Application.DisplayAlerts = False

        

            Count = Application.InputBox _
             (Prompt:="How many sheets do you want to insert?", _
             Title:="HOW MANY?", Type:=1 + 8)

             

    On Error GoTo 0

        Application.DisplayAlerts = True



    If IsNumeric(Count) And Count <> 0 Then
        For lngIndex = 1 To Count
            Worksheets.Add After:=Worksheets(Worksheets.Count)
        Next
    Else

       Exit Sub

    End If

End Sub

[royUK]

Try this

Option Explicit

Sub CreateSheets()
Dim iCount
Dim i As Integer
  On Error Resume Next
        Application.DisplayAlerts = False

            iCount = Application.InputBox _
             (Prompt:="How many sheets do you want to insert?", _
             Title:="HOW MANY?", Type:=1 + 8)

  On Error GoTo 0

        Application.DisplayAlerts = True
    If IsNumeric(iCount) And iCount <> 0 Then
 For i = 1 To iCount
      Worksheets.Add After:=Worksheets(Worksheets.Count)
      Next i
    Else: Exit Sub
    End If
End Sub

[watersev]

Sub CreateSheets()
Dim Count

    On Error Resume Next

        Application.DisplayAlerts = False

        

            Count = Application.InputBox _
             (Prompt:="How many sheets do you want to insert?", _
             Title:="HOW MANY?", Type:=1 + 8)

             

    On Error GoTo 0

        Application.DisplayAlerts = True



    If IsNumeric(Count) And Count <> 0 Then

        Worksheets.Add Count:=Count, After:=Worksheets(Worksheets.Count)

    Else

       Exit Sub

    End If
End Sub

[jomili]

Wow, way much help! Thanks.

All three macros posted seem to work magnificently, but I still don't understand why mine didn't.

Andy and Roy,
Would you explain why the "For lngIndex = 1 To Count", or the "For i = 1 To iCount"
is necessary? I can see that it is, just not why.

Watersev,
You went a different route by putting "Count:=Count," in the middle of the ADD line. Can you explain why this was necessary, why Excel wouldn't have known that Count equaled Count?

Thanks,
John

[Andy Pope]

As everybody has pointed out your code did not use the Count variable.

I think you are confused about what the use of Count in this line is doing.
Which is to position the newly added worksheet after the last worksheet.

        Worksheets.Add After:=Worksheets(Worksheets.Count)

Roy and I used a loop to add the correct number of sheets. But as watersev's code shows a loop is not required as there is a parameter to define the number of sheets to add.

[jomili]

You are correct that I am confused; it's my perpetual state of being.

However, going to the line you pointed out, I would think the "Add After:=" would take care of telling how to position the sheet, so the ":=Worksheets(Worksheets.Count)" should do something, which at face value to me looks like it's defining how many of the Worksheets to add. But you're saying that's not the case with this bit of code?

[Andy Pope]

The After parameter does do something. It tells the method after which sheet then newly added one should be placed.

The .worksheets(.worksheets.count) part identifies that sheet.

.worksheets.count will return the index number of worksheets currently in the workbook. That value is then used to index the worksheets collection.

[romperstomper]

Correct.

[jomili]

If you could see me now you'd see a big lightbulb over my head! I think I've got it now.

Thank you so much for patience and skill at explaining, and please accept my apologies for being particularly obtuse on this issue.

Thanks,
John