?
Solved

Find specific word in cell A1 in worksheets

Posted on 2014-03-18
9
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 31

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 31

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

765 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