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

[bsnell1993]

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.

 

[asjmoron]

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]

What I have to Work With.
My Data Validation Selection cell is located in cell C15 of my "GC's" Work sheet

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

My Worksheet Labels

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.

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

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

hopefully this makes sense of what you are looking for.

 

[asjmoron]

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.

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]

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]

Something like this could help then, just tweak as you need it

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