Solved

Find specific word in cell A1 in worksheets

Posted on 2014-03-18
9
260 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

808 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