Need to Copy/Paste Worksheets based on Number selected

tbakbradley

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

tazzmacd

New Member
Joined
Aug 27, 2010
Messages
22
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

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

New Member
Joined
Aug 27, 2010
Messages
22
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

Board Regular
Joined
Sep 24, 2010
Messages
126
Thanks for the recommendations. I used the Call function and was able to do exactly what I wanted.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,912
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top