How to copy worksheets with a user defined variable

  • Thread starter Thread starter Greg K
  • Start date Start date
G

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?
 
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
 
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?
 
Back
Top