Rename SHeet on Cell value condition

[parth007]

Hello Friends,
I am making a automated package.. i am done with other code..
Just a query
I have Excel Workbook which have many worksheets
what i do here is that I look for Sheet.Range("A1") data i.e. "Transport Plan -" in all sheets and if any sheet have "Transport Plan -" in Sheet.Range("A1") i rename that sheet name to "All Transport_Data"
New changes came in as below...
1) If any sheet.range("A1") = "Transport Plan -" then rename that sheet name to "All Transport_Data"
2) IF we didnot find sheet.range("A1") = "Transport Plan -" in any of the sheets then
Check for new name i.e.
sheet.range("A1") = "All Plan -" then rename that sheet name to "All Transport_Data"
Below if the current code which satisfies first condition.. need to add 2nd condition too.. please suggest
'''''''''''' Code Start
If ActiveWorkbook.Worksheets.Count = 1 Then
ActiveWorkbook.Worksheets(1).Name = "Sheet1"
Else
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Range("A1") Like "*Transport*Plan*-*" Then
Sheet.Name = "All Transport_Data"
End If
Exit For
End If
Next Sheet
End If
'''''''''''' Code End

 

[Nebu]

Try the below
Not tested ...

If ActiveWorkbook.Worksheets.Count = 1 Then
ActiveWorkbook.Worksheets(1).Name = "Sheet1"
Else
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Range("A1") Like "*Transport*Plan*-*" Then
Sheet.Name = "All Transport_Data"
Elseif " Your second condition here"
End If
Exit For
End If
Next Sheet
End If

 

[parth007]

Hi Nebu, I tried this option very earlier..
Problem here is that the condition 1 should be applied on all sheet & incase its not satisfied condition2 should come in picture..
By putting Endif the condition2 is quite to be possible even if condition1 is satisfied later..
either of condition should be satisfied...

 

[parth007]

Attached is sample..
The workbook may have 100 or more sheets
If we see on A1 in each sheet we find that
Sheet2 have "All Plan - "
and
Sheet4 have "Transport Plan - "

Code should be such that it should check for "Transport Plan - " in all sheet sheets & if found then Sheet.Name = "All Transport_Data"
If none of the sheets have A1 = "Transport Plan - "
then
Second condition is to check for "All Plan - " in all sheet sheets & if found then Sheet.Name = "All Transport_Data"
If none of the sheets have A1 = "Transport Plan - "

 

[Nebu]

Hi:

Please find the attached, hope this is what you are looking for. I have made a small change to the code you already have and also brought in some naming conventions which you have to follow. No two sheets can have the same name so I indexed the sheets based on the sheet number. Please go through the code and let me know about your thoughts.

Thanks

 

[parth007]

Hi Nebu... it dont work.. it renames both sheets .. rather it should rename only one sheet...
If condition 1 meets then ok.. if not then only second condition

 

[Nebu]

ok, if I understand correctly if you have any sheets with "Transport Plan - " you rename all those sheets with the name "All Transport_Data" and exit the for loop you won't rename sheets with "All Plan - a" But if you do not find "Transport Plan - " then you check for "All Plan - a" and rename those sheets as "All Transport_Data".

Try the below code if you have multiple sheet with "Transport Plan - " in first condition or "All Plan - a" in second condition trying indexing it as I had done in the sheet attached in above post.

Sub test()
Dim sheet As Worksheet

If ActiveWorkbook.Worksheets.Count = 1 Then
ActiveWorkbook.Worksheets(1).Name = "Sheet1"
Else
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Range("A1") Like "Transport Plan - " Then
sheet.Name = "All Transport_Data"
End If
Next sheet
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Range("A1") Like "All Plan -  " And sheet.Name <> "All Transport_Data" Then
sheet.Name = "All Transport_Data" & Right(sheet.Name, 1)
End If
Next sheet
End If
End Sub

Thanks

 

[parth007]

Thanks Nebu the code works fine.. but as i check it still renames both the sheets..
I will be be having only one sheet by name "All Transport_Data"
and other sheet by name "All Plan"

either of both can be absent too..

 

[Nebu]

Hi:

I am really not sure what you mean by renamed both the sheets. Here is the file attached. When I ran the macro it renamed only one sheet.

Thanks

 

[parth007]

Hi Nebu... below code did worked.. thanks for all help..

bCondi = False
If ActiveWorkbook.Worksheets.Count = 1 Then
  ActiveWorkbook.Worksheets(1).Name = "Sheet1"
ElseIf bCondi = False Then
  For Each Sheet In ActiveWorkbook.Worksheets
 
  If InStr(1, Sheet.Range("A1"), "Transport Plan - ", vbTextCompare) > 0 Then
  Sheet.Name = "All Transport_Data"
  bCondi = True
  Exit For
  End If
 
  Next Sheet
End If
If bCondi = False Then
  For Each Sheet In ActiveWorkbook.Worksheets
 
  If InStr(1, Sheet.Range("A1"), "All Plan- ", vbTextCompare) > 0 Then
  Sheet.Name = "All Transport_Data"
  Exit For
  End If
 
  Next Sheet
End If