Rename Sheet Name 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

[raj85]

Use below code correct it if you want any changes as I have not tested it.



If ActiveWorkbook.Worksheets.Count = 1 Then

ActiveWorkbook.Worksheets(1).Name = "Sheet1"
Else

For Each Sheet In ActiveWorkbook.Worksheets

If instr(1,Sheet.Range("A1"), "Transport Plan -",VBTextCompare) > 0 Or instr(1,Sheet.Range("A1"), "All Plan -",VBTextCompare) > 0 Then
Sheet.Name = "All Transport_Data"
End If
Exit For

End If

Next Sheet
End If

[parth007]

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 - "

[raj85]

Please use below code



Sub test()


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




End Sub

[parth007]

Raj85.. ausome man.. it worked along with my other code... thanks a lot !!!!

[raj85]

You are welcome.....