IF then GoTo

[richard.kinkead]

I have a summary sheet and one sheet for each month.
I have formatted a cell, sheet1("Z1") to display only the month value
and inserted =Today() in the formula bar for that cell.
I am trying to use "IF" in a macro to goto the appropriate month Sheet
based on the value of Sheet1 ("Z1").

 

[Ken Johnson]

I have a summary sheet and one sheet for each month.
I have formatted a cell, sheet1("Z1") to display only the month value
and inserted =Today() in the formula bar for that cell.
I am trying to use "IF" in a macro to goto the appropriate month Sheet
based on the value of Sheet1 ("Z1").

Hi Richard,

In VBA, with Today() in Sheet1!Z1...

MonthName(Month(Sheet1.Range("Z1")),False)

currently returns "November"

MonthName(Month(Sheet1.Range("Z1")),True)

currently returns "Nov"

Does this help?

Ken Johnson

 

[Guest]

Hi Richard

to move to a specific worksheet you can use:

worksheets("worksheetname").activate

you have 12 sheets maybe named

month1, month2, month3...
or
jan,feb,mar...

if it is the first case then try

activeworkbooks.worksheets("month" & month(Now)).activate

or if it is the second you can try:

activeworkbooks.worksheets("month" & format(month(Now),"mmm")).activate

or

select case (activeworkbooks.worksheets("total").range("Z1"))
case 1: activeworkbooks.worksheets("month1").activate
case 2: activeworkbooks.worksheets("month2").activate
case 3: activeworkbooks.worksheets("month3").activate
'...
case else: 'some error
end select

 

[Ken Johnson]

Hi Richard,

In VBA, with Today() in Sheet1!Z1...

MonthName(Month(Sheet1.Range("Z1")),False)

currently returns "November"

MonthName(Month(Sheet1.Range("Z1")),True)

currently returns "Nov"

Does this help?

Ken Johnson

Hi Richard,

Also, to use a macro to goto the appropriate worksheet, assuming the
full month name is used on each sheet tab, ie November not Nov...

Public Sub GotoMonthSheet()
Worksheets(MonthName(Month(Sheet1.Range("z1")), False)).Activate
End Sub

will take you there.

Ken Johnson