tbakbradley
Board Regular
- Joined
- Sep 24, 2010
- Messages
- 130
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.
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.