Rename Multiple Work Sheets using Macro based on a single Data Validation Drop Down in one Work sheet.

bsnell1993

New Member
Joined
Dec 5, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I work for a construction company that does a variety of different projects, and we are trying to streamline our bidding document to cover all city locations as well as all types of projects that we construct. I am trying to create a VBA that renames the worksheet tabs based on the type of project that it is. i.e. Multi Family, Commercial, Fit Up, or Mixed Use. I have a single sheet that serves as the project information sheet which has a Data Validation list in Cell "C15" which allows us to select "A-D" which corresponds to the types of projects we do. i.e. A=Multifamily, B=Commercial, C=Fit Up, D=Mixed Use. I would like to be able to select the respective data validation that corresponds to the type of project that we are doing and have it change the names of my worksheets. However my worksheets are also named uniquely i.e. - "Multifamily", "Multifamily GC's", & "Multifamily - Take off" There are more tabs, but they do not need to be renamed.

Everything I have found online either changes the worksheet name according to what is in that individual cell that i am trying to reference (C15=A therefore Worksheet name =A)or it is a repetition of the cell value with ascending numbers ( Multifamily1, Multifamily2, Multifamily3, Etc. ). So i am in a little bit of an odd spot. I also do not have a lot of VBA or Macro experience, just what i have been able to find on google & stumble through.

I know the data validation (letter = the type of project) makes it a bit more difficult, but there are a lot of other equations that are dictated by the data validation Letter that makes me not want to change it.

If anyone can help me try to write VBA/ Macro code for this it would be greatly appreciated.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

asjmoron

Board Regular
Joined
Apr 26, 2016
Messages
92
It may be good to see an example of what you have and what you are hoping to acheive. I have recently built something that sounds very similar for a project management company so I may be able to help with examples.
 

bsnell1993

New Member
Joined
Dec 5, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
What I have to Work With.
My Data Validation Selection cell is located in cell C15 of my "GC's" Work sheet
1575649482099.png

My Data validation list selection would be A, B, C, or D

My Worksheet Labels
1575649547297.png


I do not have any VBA or Macro figured out for this currently.


What I want to do.

If my Building Type is A i want the worksheet labels to Stay as is
If my building Type is B i want the worksheet labels to look like below.
1575649730679.png

If my Building Type is C i want the worksheet labels to look like below.
1575649780242.png

If my Building Type is D i want the worksheet labels to look like below.
1575649840891.png



hopefully this makes sense of what you are looking for.
 

asjmoron

Board Regular
Joined
Apr 26, 2016
Messages
92
This may not be any help but assuming you start with no tabs you could use the below. All you would need to do is add a button/shape/icon to assign the macro to.

VBA Code:
Sub newsheet()

If Range("c15").Value = "A - Multifamily" Then

Sheets.Add.Name = "GC's"
Sheets.Add.Name = "Apartments"
Sheets.Add.Name = "Value Engineering"
Sheets.Add.Name = "Alternatives"
Sheets.Add.Name = "Building Permits & Free rates"
Sheets.Add.Name = "Apartments - Take Off"

End If

If Range("c15").Value = "B - Commercial" Then

Sheets.Add.Name = "GC's"
Sheets.Add.Name = "Building"
Sheets.Add.Name = "Value Engineering"
Sheets.Add.Name = "Alternatives"
Sheets.Add.Name = "Building Permits & Free rates"
Sheets.Add.Name = "Building - Take Off"

End If

If Range("c15").Value = "C - Fitup" Then

Sheets.Add.Name = "GC's"
Sheets.Add.Name = "Fit Up"
Sheets.Add.Name = "Value Engineering"
Sheets.Add.Name = "Alternatives"
Sheets.Add.Name = "Building Permits & Free rates"
Sheets.Add.Name = "Fit Up - Take Off"

End If

If Range("c15").Value = "D - Mixed Use" Then

Sheets.Add.Name = "GC's"
Sheets.Add.Name = "Mixed Use"
Sheets.Add.Name = "Value Engineering"
Sheets.Add.Name = "Alternatives"
Sheets.Add.Name = "Building Permits & Free rates"
Sheets.Add.Name = "Mixed Use - Take Off"

End If

End Sub
 

bsnell1993

New Member
Joined
Dec 5, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I'm looking for something that just renames the tabs, as all of my tabs have equations & a lot of Data already input into them. so i wouldn't want to be creating new ones.
 

asjmoron

Board Regular
Joined
Apr 26, 2016
Messages
92
Something like this could help then, just tweak as you need it

VBA Code:
Sub newsheet()

If Range("c15").Value = "B - Commercial" Then

Sheets("Apartments").Name = "Building"
Sheets("Apartments - Take Off").Name = "Building - Take Off"

End If

If Range("c15").Value = "C - Fitup" Then

Sheets("Apartments").Name = "Fit Up"
Sheets("Apartments - Take Off").Name = "Fit Up - Take Off"

End If

If Range("c15").Value = "D - Mixed Use" Then

Sheets("Apartments").Name = "Mixed Use"
Sheets("Apartments - Take Off").Name = "Mixed Use - Take Off"

End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

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