Solved

Find specific word in cell A1 in worksheets

Posted on 2014-03-18
9
259 Views
Last Modified: 2014-03-18
I have a workbook with several worksheets which when saved down are saved with different names each time. [please don't ask why :-)]

I need to find the word Summary which is in cell A1 in one of the worksheet and then rename the worksheet to Summary

Could an expert provide me with the VBA code to do this please.

Thanks
0
Comment
Question by:Jagwarman
  • 6
  • 3
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39936262
YES TRY THIS

Dim WS as Worksheet

For each WS in Activeworkbook.worksheets
      if WS.Range("A1") = "Summary" then
            if WS.name <> "Summary" then WS.Name = "Summary"
            Exit For
      end if
Next WS

Open in new window


gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39936273
Alternatively, if your Cell A1 maybe contain the word Summary like Summary 2012 Figures then the following code is more appropriate.

Dim WS as Worksheet

For each WS in Activeworkbook.worksheets
      if instr(1,lcase(WS.Range("A1")),"summary") <>0 then
            if WS.name <> "Summary" then WS.Name = "Summary"
            Exit For
      end if
Next WS

Open in new window


gowflow
0
 

Author Comment

by:Jagwarman
ID: 39936274
Hi gowflow that works a treat. Just in case [because these things keep coming back and biting me on the nose] is it possible to change it from A1 to a Range say A1:K10

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39936278
Here it is

Sub FindSummary()
Dim WS As Worksheet
Dim cCell As Range

For Each WS In ActiveWorkbook.Worksheets
    For Each cCell In WS.Range("A1:K10")
        If InStr(1, LCase(cCell), "summary") <> 0 Then
              If WS.Name <> "Summary" Then WS.Name = "Summary"
              Exit For
        End If
    End If
Next WS

End Sub

Open in new window



gowflow
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 39936280
You have to be very careful on this as if the word summary comes in this range in several worksheets then it is the first occurrence of this word that the macro will rename the sheet.

Now if by chance this sheet already exist then we need to treat this differently.
gowlfow
0
 

Author Comment

by:Jagwarman
ID: 39936369
Hi gowflow,

that throws up an error End if without block if but I can't figure where the if needs to go

Also, I am happy that it stops at the first so, as you say, what if the sheet summary already exists??

tks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39936408
Yes sorry my mistake instead of putting Next cCell I put End if !!! :(

This code is more stable as it take into consideration the possibility of sheet summary already being there so it will not fall in the trap of renaming an other sheet that contains the word summary. and if it find the first occurrence of summary in the range (A1 to K10) then it will rename the sheet to summary and let you know.

Try it all possible ways.

Sub FindSummary()
Dim WS As Worksheet
Dim cCell As Range
Dim TmpShtName As String

For Each WS In ActiveWorkbook.Worksheets
    For Each cCell In WS.Range("A1:K10")
        If InStr(1, LCase(cCell), "summary") <> 0 Then
            If WS.Name <> "Summary" Then
                On Error Resume Next
                TmpShtName = WS.Name
                WS.Name = "Summary"
                If Err <> 0 Then
                    WS.Name = TmpShtName
                    MsgBox "Sheet Summary already Exist, cannot rename sheet: " & TmpShtName & " to 'Summary'", vbCritical
                Else
                    MsgBox "Sheet: " & TmpShtName & " was renamed to " & WS.Name & " Successfully", vbInformation
                End If
                On Error GoTo 0
                Exit For
            End If
        End If
    Next cCell
Next WS

End Sub

Open in new window


gowflow
0
 

Author Closing Comment

by:Jagwarman
ID: 39936471
That's brilliant thanks for all the work you put into this for me
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39936477
Your welcome my pleasure !
feel free to put a link in here for any question you may need help with.

Rgds/gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel partial font macro 14 26
Excel Score Formula 5 49
Easy Excel formula needed 4 26
Delete all charts except for two charts with a specific name using VBA 8 13
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now