Select Sheet Based on Cell Value

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
Hello all,

While I've been an avid browser for years, this is my first posting of a question to any forum ever. I normally google search my problem, as most problems have occured before, but I'm having trouble getting this one to work specifically. Any help would be appreciated.

I'm creating a macro to populate an inventory workbook with 32 sheets on a daily basis (Sheets = 1, 2, 3, ..., 31, Data). The idea is, a report is copy/pasted into the Data sheet (daily) and then the macro populates the data into the appropriate Day sheet (daily). So I need code to select the appropriate Sheet, based upon a date given in the data.

'**Tried having the value stored, as opposed to the formula that exists in R1 (R1=DAY(A1)).**


Sheets("Data").Select
Range("R1").Select
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Dim DayNum As Long
Worksheets("Data").Activate
DayNum = Cells(2, "R").Value
Range("R3") = DayNum '****this works so I know DayNum exists

Sheets("DayNum").Select


'**Currently, R2 contains the value "1" and I have a Sheet names "1"**

The last line, selecting the appropriate sheet, is where it fails; I receive a "run-time error". I have tried various forms of formatting on R2 (text, general, number) to no avail.

Again, thank you in advance to help a fledgling programmer.

Sevy
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board..

This line
Sheets("DayNum").Select
Is looking for a sheet literally named "DayNum"..Not a sheet named according to the value of your variable DayNum

Remove the quotes to make it use the Variable DayNum
Sheets(DayNum).Select
 

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
Jonmo1

Thank you, it works fine now. I feel embarrassed at its simplicity now. I did try that at one point, but it must have been before other changes were made.

Sevy
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Another problem you have is the use of numbers 1 to 31 as sheet names.

Excel keeps a list of Index #s for the sheets.
This is based on the order the sheets are displayed from left to right.
The sheet that is furthest left is Index #1
The 2nd sheet is index #2
etc..
These index numbers may not correlate to the actual Tab names you have created 1 to 31.

So if DayNum = 3
Sheets(DayNum).Select will select the 3rd sheet from the left, not necessarily the sheet NAMED "3"

To resolve this, change
Dim DayNum As Long
to
Dim DayNum As String
 

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
Thank you again Jonmo1. I have been trying to figure out the basics of the being a member on this forum and had not had time to run it. Your correction was needed and appreciated.

Sevy
 

MikeO26

New Member
Joined
May 2, 2014
Messages
1
Hello

I was looking for code to help me select sheet based on cell value and it brought me to his thread, I'm quite new to macro and only knows basic. My spreadsheet is somewhat similar to yours instead I change my value from dropdown list, then one sheet unhide and the rest are hidden except the other sheet which is for the menu.


Sub Unhideone()

Unhideone Macro


Dim strName As String

strName = Range("B4")
‘ B4 is a dropdown list
Sheets(strName).Select

Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("stName").visible = true
End Sub

If I choose sheet1, sheet 2 and sheet 3 will hide. If I choose sheet 2, sheet 1 and sheet 3 hides.

Hopefully you can help. Thanks!
 

Forum statistics

Threads
1,082,250
Messages
5,364,036
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top