Macro that creates sheets based on list

Hi everyone,

I'm trying to create a macro that will generate different sheets based on a list.

For example:

I have a button on one sheet that selects a location; based on each location, the trucking company will change.

Location 1 List:
Trucking Company 1
Trucking Company 2
Trucking Company 3

Location 2 List:
Trucking Company 1
Trucking Company 2
Trucking Company 3
Trucking Company 4
Trucking Company 5

I'm looking for a macro that I can run that will create sheets that are named based on the company (ex. Creates a tab called "Trucking Company 3"). I have a main tab that has an analysis of ALL companies, ideally I would like the macro to copy this tab, re-name it to Trucking Company XX, and input the name of the company into cell XX.

Does anyone have an idea on this? Any help is much appreciated, let me know if clarification would help.

THANKS!!

Is this what you're looking for?

Please Login or Register  to view this content.

Sample workbook is attached

That did fix the run-time error...however, it just created 29 tabs called "Carrier - Current Location"

Is there a way for the tab to be named the same as the company in cell c4 and have it stop running when it becomes blank running down the list?

Also, when the tab gets created, I want it to be the copy of a different tab (named "Carrier Specific") and enter a value (the same as the company in c4) into another cell (b5).

Sorry for being so picky...I know this is asking a lot!

Any help at all is very much so appreciated

C4 is the number 19, based on the image you uploaded. You want to name it 19? then the next sheet as 15? and so on until you reach a blank cell? My code I uploaded names the sheets based upon the cell value, have you tweaked any other parts of the code? Also, it may be easier if you could upload a sample workbook

Crap...I meant B4, the 4 digit alpha code. Sorry for the confusion :/


I would want it to go CKUO, HJBI, WENP, FTAT, TMUQ, and then stop running.

I don't believe anything else was tweaked

Change C4 to B4, and change the 3 to a 2. Let me know if that works

The rest of the code you want I can figure out relatively quickly, but I'm at work and want to go home Let's get the create new sheets part down and then if no one else chimes in, I'll get you the rest of what you're wanting later tonight or tomorrow

Hi again everyone, and thanks again for the help. I ran the code as recommended by stkynts and it ended up creating about 25 tabs before it had a run-time error. All of the tabs were named "carrier - current location" again.

It highlighted this line as the error in debugging mode -- Sheets(Sheets.Count).Name = x.Value

Any thoughts? Thanks!

How odd. I made a test sheet with all the information you provided in the image from a previous post and it worked just fine for me. There is probably an important piece of information you are not sharing with us

I just went back through and read the previous posts. Correct me if I'm wrong but you dont need just a sheet created for column B do you? You need sheets for whats in Victorville, Dallas, and whats below CPU Carrier on both. If thats correct then this is going to be a lot more complex. Still do'able but more complex.

stnkynts -- I confused myself earlier and created more confusion for those helping me due to similar tab names

Here is some clarification:

Here is the sheet it will be based off of : sheet.JPG

This sheet is named "Carrier - Current Location"
--It's cells are filled based off of a previous macro that fills the 4-digit code based on location from a different sheet ("Carriers - All Locations")
--(ex. in this case, this is the list for Victorville)

I need to run a macro that will create a new sheet for each name in this list (CKUO, HJBI, WENP, FTAT, and TMUQ) and then stop at the 0 cell.
--I want the new sheet to be named after it's respective 4-digit code.
--I then also want to enter this 4-digit code into a cell (we'll say C4) onto the new sheet.

Really sorry for creating the confusion--does this clear it up?

Hmm, this time it sort of froze for a minute or two, then just created 5 sheets called 'Carrier - Current Location'

Is there something I'm doing wrong? :/

Not sure. Start a new, completely empty, test workbook and put the data in the exact cells as your picture. Run the macro and see if it does what you want. If it works then there is something else causing problems.

Thanks again for the help, but perhaps something is wrong with my workbook...I'll try starting over:

===============

Hi everyone,

I mentioned a similar problem before, but I did a poor / confusing job of explaining it, so it it goes:

I have 2 sheets:
(1) Carrier - Current Location as seen here : sheet.JPG
(2) Carrier Specific

Here is what I'm hoping the macro will accomplish:
1) Copy the sheet called Carrier Specific
2) Name the new sheet based on the value in Carrier - Current Location (starting at C4)
3) Input that same value into cell D1 of the new sheet
4) Go down the list in Carrier - Current Location and create new sheets like this until it hits a 0 value

Ex. Carrier - Current Location C4 = CKUO
--I want this to copy Carrier Specific, re-name is CKUO, and input "CKUO" into cell D1 of the new sheet called CKUO
--Then repeat this for every item in the list until it hits a value of 0

I attached my spreadsheet to this as well, please let me know if you can help me out / lead me in the correct direction!
(It's a smaller version of my real workbook, so there's a bunch of REF errors)

Thanks so much!!

Maybe:

Please Login or Register  to view this content.

Worked with spreadsheet you provided.

Please Login or Register  to view this content.

@stnkynts --thanks so much, I think this works!!

I just recently started getting into VBA for some work related stuff--are there any books or materials that you would recommend to learn a bit more in depth?

Thanks again to everyone who helped, I truly appreciate it!

One question about this---this code is part of a larger macro I have. After it's complete, I have it set to hide the sheets, but no code seems to execute after these tabs get created.

I have the ("SHEET NAME").Visible = False before the 'End Sub', is something else stopping this from executing?

In response to your first question about learning VBA I think i started with VBA for dummies then just progressed from there. The biggest teacher has actually been trial and error with forums like this. I would look at what people wanted help with then I would try to do it myself. One of the pro's would of course finish it faster than me so I would compare to see how to become more efficient. That's the thing with coding in general. There are always a lot of ways to do something, just try finding the most efficient. About your second question, your additional coding is not going to work because I threw in the exit sub when it hits a 0 since you said that's the end. I did this to speed things up. If you want to hide these sheets after they are created just put the one line of code in:

Please Login or Register  to view this content.

Keep in mind if you run this macro more than one and the sheets stay hidden you are gonna run into some errors i believe. Hope this helps.