Tab Name Based on Document Search

[MattH1]

Good morning. I'm looking to find the name of a certain tab in my document (to make a reference point in my code) that is found by checking the first cell (1,1) of each tab for "Keywords".

Basically I have four tabs that have a bunch of info and I want the "active sheet" to become whatever sheet has "Keywords" in Cell(1,1) and for me to be able to reference that sheet later in the coding at any point. The tab name is dynamic though, as it'll change from week to week.

If I can help by being any more specific, I appreciate it!

I know that my code will eventually have to use this line of code (or something similar):

TabName = ActiveSheet.Name

But I would have to make sure the correct tab is active and that is only by finding the keyword in R1C1

Thanks!!

 

[Kamolga]

Good morning. I'm looking to find the name of a certain tab in my document (to make a reference point in my code) that is found by checking the first cell (1,1) of each tab for "Keywords".

Basically I have four tabs that have a bunch of info and I want the "active sheet" to become whatever sheet has "Keywords" in Cell(1,1) and for me to be able to reference that sheet later in the coding at any point. The tab name is dynamic though, as it'll change from week to week.

If I can help by being any more specific, I appreciate it!

I know that my code will eventually have to use this line of code (or something similar):

TabName = ActiveSheet.Name

But I would have to make sure the correct tab is active and that is only by finding the keyword in R1C1

Thanks!!

Hi,

If for example you want to refer to a sheet which is called today data1 and tomorrow might be called data2, the easiest way if in vba click on the sheet (on left in microsoft excel object) and change the first priority in the left column below:
(name):Sheet1 (or anynumber of the sheet is was) into the name you want, ShData for example.

You can then refer to ShData in your macros and users can change the name of the sheet without any impact on the macro.

Hope it helps

 

[MattH1]

Hi,

If for example you want to refer to a sheet which is called today data1 and tomorrow might be called data2, the easiest way if in vba click on the sheet (on left in microsoft excel object) and change the first priority in the left column below:
(name):Sheet1 (or anynumber of the sheet is was) into the name you want, ShData for example.

You can then refer to ShData in your macros and users can change the name of the sheet without any impact on the macro.

Hope it helps

Thank you for such a fast response! I'm actually looking to make something more dynamic. The sheet's name may be changed but they also may just delete the sheet and import a new sheet with the same value in Cell(1,1) and new data in the rest of the sheet. Therefore, I'm looking to have the document search for that exact value in A1 (or a Len statement would even work here I assume) to find the tab name.

Hope someone can help start me in the right direction, I'm always up to learn but really lost on where to start on this one? I haven't searched full documents before in VBA for a keyword, located it, and then made that an active sheet. That's what I think I need to do here.

 

[MattH1]

Any and all help is appreciated, I've been looking stuff up and still haven't gotten anywhere :/

 

[MattH1]

Sub TabName()
Dim ws As Worksheet
Dim TabName As String

For Each ws In ThisWorkbook.Worksheets
    With ws.UsedRange
        If ws.Range("A1") = "qwer" Then
            TabName = ActiveSheet.Name
        End If
    End With
Next
Sheets("Sheet1").Range("A2") = TabName
End Sub

This is the code I came up with. Sheet1 has the text "asdf", Sheet2 has the text "asdffff" and Sheet3 has the text "qwer".
It keeps outputting "Sheet1" in Cell A2. Can anyone tell me why this isn't working?

 

[MattH1]

Still haven't got an answer and looking for help!!