PDA

View Full Version : Solved: Copy worksheets with msg box



Pam in TX
10-11-2007, 02:20 PM
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.....


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


EDIT: Added VBA tags Tommy

Tommy
10-11-2007, 02:39 PM
Hi Pam in TX :hi:

Try This :)


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

Pam in TX
10-11-2007, 02:48 PM
Perfect...... Thanks neighbor.......... :hi:

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

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

lucas
10-11-2007, 03:17 PM
Too many longhorns in this thread for this okie to be comfortable...:devil2: