VBA to input new sheets to workbook

[zakynthos]

How would I modify this code to populate the workbook I'm using with variable no of new worksheets (not into a new workbook as below?

I would like to add 100 tabs with the generic name Recipient' after the last existing tab in the workbook

Many thanks!

'DECLARE VARIABLES
'Stores the users preferred no. of sheets and names workbook so we can refer back to itAllows us to loop through the new sheets and rename them
Dim lngPref  As Long, wkbNew As Workbook
Dim strPrefix As String, intSheets As Integer, i As Integer

'CAPTURE USER PREFERENCES
'Populates lngPref with application sheets in new workbook
'Ensures the 4 sheets set application to 4
'We create a new wkb workbook inside our new  wkb variable

lngPref = Application.SheetsInNewWorkbook
strPrefix = InputBox("Please enter your prefix", "New Workbook")
intSheets = InputBox("Number of sheets required", "New Workbook")

'CREATE WORKBOOK CONTAINING 4 WORKSHEETS
Application.SheetsInNewWorkbook = intSheets
Set wkbNew = Application.Workbooks.Add

'NAME NEW SHEETS IN WORKBOOK
'We use the For ..Next loop to rename the sheets in our new workbook
'To create the name we concatenate the current value with our integer variable
'The integer variable is used both to target the worksheet and compose the name

For i = 1 To intSheets
wkbNew.Worksheets(i).Name = strPrefix & "month1" & i
Next

'RESTORE USER PREFERENCES
'We simply reverse the line of code we used to capture user's preferences in the first place:

Application.SheetsInNewWorkbook = lngPref

'Finally , we 'll allow the user to choose the prefix for the new worksheets as well as the number of sheets.  We'll need to declare 2 more variables to do this:
'Dim strPrefix As String, intSheets As Integer

'We populate both these variables using the inputbox statement:
'strPrefix = InputBox("Please enter your prefix", "New Workbook")
'intSheets = InputBox("Number of sheets required", "New Workbook")
'we also need to change the no,. of sheets in the new workbook from 4 to 'intsheets'
'Finally, we need to replace the 2 fixed values in our For loop:
'The 4 is replaced with intSheets and Quarter is replaced with strPrefix

'To include a space between week and integer we would simply add an &  "" after strPrefix
End Sub

 

[Yahya]

may be this code will help

Sub Test()
Dim ShNeed As Integer, i As Integer
ShNeed = InputBox("Insert number of sheets you want ")
For i = 1 To ShNeed
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Recipient " & Sheets.Count + 1
Next i
End Sub

 

[Ruddles]

The short answer is: I wodn't modify that code at all - I'd write some from scratch. You just need something like this:-

[FONT=Fixedsys]Dim x[/FONT]
 
[FONT=Fixedsys][FONT=Fixedsys]With ThisWorkbook[/FONT]
  For x = 1 To 100[/FONT]
[FONT=Fixedsys]   .Worksheets.Add After:=.Worksheets(.Worksheets.Count)[/FONT]
[FONT=Fixedsys]   .Worksheets(.Worksheets.Count).Name = "Recipient " & x[/FONT]
[FONT=Fixedsys] Next x[/FONT]
[FONT=Fixedsys] .Worksheets(1).Activate[/FONT]
[FONT=Fixedsys]End With[/FONT]

 

[zakynthos]

That's works great!

Thanks very much, and for the quick reply