find worksheet based on string in a cell

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
Hello all, and thanks in advance
I'm writing a small macro and am beating my head against the wall on a simple problem. (Excel 2016, PC, been 3 years since I've done any VBA).
I have data from 2 cells (A1 and B1) that I have concatentated into cell A3. The string in A3 now matches the name of one of the worksheets in the workbook. The number of worksheets is dynamic. I simply want to find and select the applicable worksheet so the user can view it.

How I remembered how to do everything up to this point and yet forget how to do this is beyond me.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this ("Your_Sheet" is where the cell A3 is):

Code:
Sheets(Sheets("[COLOR=#ff0000]Your_Sheet[/COLOR]").Range("A3").Value).Select
 
Last edited:
Upvote 0
...if I may trouble you again...
My next step is to find a cell in column c on the activesheet we've just selected that matches the string in column d on "Sheet2" (which is also the value of ComboBox3 in a user form - whichever is easier). I want to find the cell and make it active.

I've tried the following code and get no errors, but I get no results either.
'active stands for active ingredient

Code:
Dim active as String
active = Application.WorksheetFunction.Vlookup(ComboBox3.value, ActiveSheet.Range("C:C"), 1, False)


fyi-i'm new to, and horrible at, Vlookup

Again much thanks to any who read this
 
Upvote 0
it actually doesn't have to make the cell active, it can make the entire row active. This will set me up for the next step which is going to read something like this:

Code:
With ActiveSheet
If Textbox1.value . ActiveSheet.Range("F(Activerow)") Then
Userform2.Label7.ForeColor = vbRed
Else
Useerform2.Label6.Forecolr = vbGreen
End IF
End With
 
Upvote 0
...My next step is to find a cell in column c on the activesheet we've just selected that matches the string in column d on "Sheet2"...
it actually doesn't have to make the cell active, it can make the entire row active...
See if this works for you (adjust D4 to suit your needs):
Code:
...
ActiveSheet.Range("C:C").Find(Sheets("Sheet2").Range("[COLOR=#FF0000]D4[/COLOR]"), _
    LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).EntireRow.Select
...
 
Upvote 0
again awesome! So, how will I address the "Active row" in my next piece of code (I posted it a few minutes ago - incorrectly)
Basically i'm checking to see if the value in a textbox is greater than or less than the value of the ActiveRow in Column F
 
Upvote 0
so I'm on a little bit different topic than the original post but not sure if I should start a new thread or not....
Using the above help from Tetra101 (who has been a fantastic helper) I've come to the following issue - How to reference an "Activerow" in a range? I think I've learned to use "Target" instead.On the following code:
Code:
With Activesheet
If Textbox1.Value > activesheet.range("J" & Target.row) Then
Userform.label7.forecolr = vbRed
Else
Userform2.Label6.Forecolor = vbGreen
End If
End With


when I run this I get a Run Time error '424 Object required

Any help is greatly appreciated
 
Upvote 0
Figured it out.
Instead of using "target" I used "ActiveCell".

Thanks to all for everything along the way!!
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top