Solved: Copy worksheets with msg box

[Pam in TX]

I need assistance with the following Macro......... I want to create multiple copies of a sheet called "data" based on variable criteria....place them after the data sheet.... If they could be renamed data1, data2, etc. that would be an added bonus...... What I have now simply creates blank sheets............. Thanks in advance.....

[VBA]
Sub AddHowMany()
' Turns the screen off while the Macro is running.
Application.ScreenUpdating = False
'Prompt user for total number of new sheets needed, then repeats based on answer
totalsheets = InputBox("How many copies of the worksheet do you want to create?")
For X = 1 To totalsheets
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=X
Next X
'Turns the screen back on when the Macro is finished.
Application.ScreenUpdating = True
End Sub
[/VBA]

EDIT: Added VBA tags Tommy

[Tommy]

Hi Pam in TX

Try This

[VBA]
Sub AddHowMany()
Dim mNewSht As Worksheet
' Turns the screen off while the Macro is running.
Application.ScreenUpdating = False
'Prompt user for total number of new sheets needed, then repeats based on answer
totalsheets = InputBox("How many copies of the worksheet do you want to create?")
ActiveSheet.Cells.Select
Selection.Copy
For x = 1 To totalsheets
Set mNewSht = Worksheets.Add(After:=Worksheets(Worksheets.Count))
mNewSht.Name = "data" & CStr(x)
mNewSht.Paste
Next x
'Turns the screen back on when the Macro is finished.
Application.ScreenUpdating = True
End Sub
[/VBA]

[Pam in TX]

Perfect...... Thanks neighbor..........

I am still very new to this...... and still run into many problems.... Maybe one day I can be smart also....

Thanks again..........

[lucas]

Too many longhorns in this thread for this okie to be comfortable...