Select Sheet Based on Cell Value

[JohnHenry]

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]

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]

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]

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]

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

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[MikeO26]

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!