Retrieving Tab Name After Searching for a Cell

[MattH1234]

Hey everyone,

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.

The end goal is I can find the active sheet I want to start at and re-reference, no matter which tab I run the macro from.

Here is what I came up with to no avail:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi and welcome to the forum.
Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I'll change it for you on this occasion but please note for the future

The code looks OK to me. The With End with is redundant and not sure why you used "qwer" rather than the word "Keywords"
And don't forget VBA is usually case sensitive so use the LCASE() to force any string to be lower case

Please Login or Register  to view this content.

[MattH1234]

I will be sure to use this information from now on! I've taken another look at the code to try seeing where the problem lies. I took a very basic document with qwer in Sheet 1, asdf in Sheet 2, and zxcv in Sheet 3 (All in Cell A1). It seems that whichever sheet I START the macro from is the output of Cell A2 in Sheet1. That means it's not activating the sheet when it FINDS "asdf" (for example) and just using the sheet I started the macro from. How do I program Excel to switch to the sheet where "asdf" is found and THEN take the sheet name?

I edited this post to make sure to state that I saw your response and I want to thank you very much for looking at it and touching it up! I am just looking to fix the last problem I'm having with that code, and that's what I stated above. The code runs fine and no errors appear, but I still have trouble activating the correct tab once it finds "asdf" (or keyword) so that I can establish that tab name as a variable for later use,

[Richard Buttrey]

Hi,

Sorry there was a typo in my original. I should have used the ws reference not the Active sheet.

Here's a corrected code. And since once you've found the relevant sheet there's no point in continuing with the loop I've put an Exit Sub in the loop.

Please Login or Register  to view this content.