How to copy worksheets with a user defined variable

[Greg K]

am a novice when it comes to creating Excel templates but I have
managed to do rather well up until now. I am trying to create a
pricing template that allows a user to define a number of locations
for a multi-location template. I need the workbook to automatically
copy a worksheet a set number of times depending on what number the
user enters. For example, if the company has 8 locations, the user
would enter the number 8 in the box and the workbook would copy the
location tab 8 times. Does anyone know how to do that or am I wanting
Excel to do something it can't?

 

[Ron de Bruin]

Try this Greg

Sub test()
myNum = Application.InputBox("Enter a number", Type:=1)
For a = 1 To myNum
Worksheets("Pricing").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Pricing " & a
Next
End Sub

 

[Greg Kidd]

Thanks a bunch. That did the trick. Now how does one get their boss to
understand that they need a raise for this kind of work when they
weren't hired for it?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!