VBA to rename tabs according to cell value (cells not always in the same place)

[Hypex]

Hi all

I see some people have asked a similar query on renaming tabs with a specific cell's content, and I found the below which almost does what I want.

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("C2")) > 0 Then
ws.Name = Replace(ws.Range("C2").Value, "Processing: ", "")
End If
On Error GoTo 0
If ws.Name <> Replace(ws.Range("C2").Value, ":", "-") Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

The only problem is that, in two of my four tabs (of course!), the cells I want to reference are in slight different places, and I want to change the names a bit more.

So, in Tab 1, I have a name like "Processing: File Type" in C2, and I want the tab to be named "FileType"
In Tab 2, I have a name like "Processing: File Issues" and in C2, and I want the tab to be called "FileIssues"
In Tab 3, I have a name like "Processing: Document Breakdown Report" in D2, and I want the tab to be called "DocumentType"
In Tab 4, I have a name like "Processing: Random Colours" in E2, and I want the tab to be called "Colours".

Something like that.

The only saving grace, is that the tabs are always in the same order, and the text is always in the cell that I mentioned above, so I am wondering if it is possible to do this.

 

[Joe4]

Welcome to the Board!

In order to do something like this automatically, we have to establish some "hard-and-fast" rules that we can follow. You cannot program anything that you cannot clearly define first.

So let's start with a few questions about the location of this cell that we want to pull the tab name from.
1. Will it always be found in row 2?
2. Will the first word in the cell that we want to pull from ALWAYS start with "Processing:"?
3. If other cells may have the word "Processing:" in them also, how do we determine which one we want?

Secondly, the values after the word "Processing:", are they ALWAYS those particular values, or might they be something else?
If they might be something else, then I see a problem, as there does not seem to be a consistent pattern in what is being pulled. In the examples you provided, I see no "hard-and-fast" rule here:
"File Type" goes to "FileType"
"File Issues" goes to "FileIssues"
"Document Breakdown Report" goes to "DocumentType" (why is "Breakdown Report" dropped and where does this word "Type come from?)
"Random Colours" goes to "Colours" (why is the word "Random" dropped?)

So unless these are the only values that these cells may contain, we need some more explanation/details here so we know how to set up the programming rules.

 

[Hypex]

Hi Joe

Thanks a lot for your reply! So, to answer:

1. Will it always be found in row 2?
Yes
2. Will the first word in the cell that we want to pull from ALWAYS start with "Processing:"?
Yes
3. If other cells may have the word "Processing:" in them also, how do we determine which one we want?
It could happen that another cell may have "Processing:". Unlikely, but not impossible. This is where I was thinking that if I know the specific cell per tab which is C1 for the first two tabs, D1 for the third, and E1 for the fourth, that this could be sufficient. These cell values won't change (it is just unfortunate that the output doesn't put them in the same cell in each sheet).

Secondly, the values after the word "Processing:", are they ALWAYS those particular values, or might they be something else?

The values are always going to be those particular values.

If they might be something else, then I see a problem, as there does not seem to be a consistent pattern in what is being pulled. In the examples you provided, I see no "hard-and-fast" rule here:
"File Type" goes to "FileType"
"File Issues" goes to "FileIssues"
"Document Breakdown Report" goes to "DocumentType" (why is "Breakdown Report" dropped and where does this word "Type come from?)
"Random Colours" goes to "Colours" (why is the word "Random" dropped?)

Yes, the new values are not always exactly reflective of the original values. The first two, e.g. "File Type" -> "FileType" make sense, but the second two, they just need to get different tab names. This is just the way that the tabs are meant to be named. This won't change either though - the tab with text "Random Colours" is always going to need to be named as "Colours". I don't want to change the text in the cell to "Colours" - that should stay the way it is, but just the tab should be called "Colours".

I hope that makes sense, and thanks again for taking the time!

 

[Joe4]

OK, if I understand you correctly, it sounds like:

Sheet1:
Value will ALWAYS be found in cell C2 and ALWAYS be "Processing: File Type", and re-name of file tab ALWAYS should be "FileType".

Sheet2:
Value will ALWAYS be found in cell D2 and ALWAYS be "Processing: File Issues", and re-name of file tab ALWAYS should be "FileIssues".

Sheet3:
Value will ALWAYS be found in cell D2 and ALWAYS be "Processing: Document Breakdown Report", and re-name of file tab ALWAYS should be "DocumentType".

Sheet4:
Value will ALWAYS be found in cell E2 and ALWAYS be "Processing: Random Colours", and re-name of file tab ALWAYS should be "Colours".

If this is indeed true, then you really don't need to pull the value from the cell, your know that:
- Sheet1 will ALWAYS be renamed to "FileType"
- Sheet2 will ALWAYS be renamed to "FileIssues"
- Sheet3 will ALWAYS be renamed to "DocumentType"
- Sheet4 will ALWAYS be renamed to "Colours"

So why does the code need to be dynamic at all, instead of just renaming the 4 tabs according to those rules listed above in blue?

 

[Hypex]

Yep - your summary is correct, except that for Sheet 2, the value will always be found in cell C2, rather than D2.

That's a very fair point! :D You're right - it doesn't have to be pulled from any cell, as I do know the expected tab names already. I guess I just wanted to take it from the cell names to ensure that there wouldn't be any issue on the very odd chance that somebody did reshuffle the tabs. I know there are only four tabs, but there are 100s of these workbooks, so I wanted a quick way to automate the renaming, rather than doing it manually.

If it is much easier to do this by pre-defining the tab names, that would also work for me!

 

[Joe4]

The thing is you are not fully taking it from the cell values, you are sort-of taking it from there, making changes to it, which aren't consistent across all 4 tabs.
That is why I am not sure it makes sense to continue on the path your original were trying to do, as opposed to assigning them directly.

If it is much easier to do this by pre-defining the tab names, that would also work for me!

So, is some process creating the file?
If so, what does that code look like?