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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
Try this ("Your_Sheet" is where the cell A3 is):

Code:
Sheets(Sheets("[COLOR=#ff0000]Your_Sheet[/COLOR]").Range("A3").Value).Select
 
Last edited:

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
...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
 

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
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
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
...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
...
 

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
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
 

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
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
 

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
Figured it out.
Instead of using "target" I used "ActiveCell".

Thanks to all for everything along the way!!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,042
Messages
5,484,353
Members
407,437
Latest member
alfaroM

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top