Solved

Find specific word in cell A1 in worksheets

Posted on 2014-03-18
9
258 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

22 Experts available now in Live!

Get 1:1 Help Now