Need to Copy/Paste Worksheets based on Number selected

[tbakbradley]

Here is code I have for a portion of my Macro that has been working for some time.

Sub BuildSheets()
'
Dim location As Integer, numtimes As Integer, ws As Worksheet

location = Application.InputBox(Prompt:="Enter number of Locations requiring Custom Bid Investments", Type:=1)

If location < 1 Then
MsgBox "Locations must be Greater than 0"
Exit Sub
End If

MsgBox "Please Rename the Location Tabs to fit your needs"

Sheets("Location").Visible = True
For numtimes = 1 To location - 1
On Error Resume Next
If Sheets("Location (" & numtimes & ")") Is Nothing Then
Set ws = Sheets("Location")
Else
Set ws = Sheets("Location (" & numtimes & ")")
On Error GoTo 0
End If
ActiveWorkbook.Sheets("Location").Copy After:=ws
Next numtimes
Sheets("Location").Select
Sheets("Location").Name = "Location (1)"
End Sub

What this does is provides the user with an input box to enter the number of Locations Required. It has to be more than 1, or it will exit the program. Based on the Number input, the macro will take the "Location" Worksheet and copy it based on the number selected. It will also add numbers and rearrange them so that each Worksheet is relabled beginning with "Location (1)", then "Location (2)", etc.

All this works great. I have a need to add another function now for ANOTHER Worksheet. However, this time, after the above has been completed, I need another box to ask "Do you require Facilities, Yes or No". If No, then it will exit the program. If "Yes", then it needs to ask the user "How many facilities are required" and then do the SAME thing as the code above, but with a worksheet named "Facilities" and not "Location". I'm having problems with this piece as I'm not sure the best method.

Any help would be appreciated.

 

[tazzmacd]

Why not build a duplicate of this Sub and call it once this sub is done? That woul dbe the easiest way

For example call it Sub Facilities() Copy the same code down but change the naming. Then at the end of the BuildSheets Function add in Call Facilities. It will then process through that sub once finished come back to the Buildsheet to finish up.

 

[tbakbradley]

Thank you, and sorry for the delay. My DSL has been down for a few days.

I thought about calling another Sub, but the code I have now was established to run as long as the number entered was at least 1. For this portion, it doesn't have to copy a spreadsheet if they don't need it. I think that's why I was thinking of the "yes/no" question.

If I were to build a new Sub, copying that same code, if they put in a '0', how would it best be handled to skip the rest of the code so it didn't copy/paste?

 

[tazzmacd]

At the end of the main BUildSheet sub tha tyou currently have, put in your questions. If they hit yes the call the new Function Sub and then as them now many times they need it to run. If they hit no then just exit out of the BuildSheet sub totally.

 

[tbakbradley]

Thanks for the recommendations. I used the Call function and was able to do exactly what I wanted.