Finding correct section on worksheet

[WLMPilot]

I have a workbook for balancing my checkbook.

Given:
1) Worksheets Named: 2004, 2005, 2006, 2007, 2008, 2009, STATS
2) Each year worksheet as the year, ie 2009, in cell A1
3) The STATS worksheet has a section for each year that summarizes that data
from its respective year. Each section has the year in column A.

I have a command button on each year worksheet, ie 2009, with caption of
STATS. Obviously I want to jump over to STATS worksheet when this button is
clicked.

Here's the trick: I want to be able to jump to the respective year STATS
information. EXAMPLE: If I am in year 2008 and click the STATS button, I
want to go directly to the 2008 section on the STATS worksheet. I know that
the macro will need to look at cell A1 on the 2008 worksheet and then find
2008 in column A on the STATS worksheet. and then select or activate. I just
don't know how to do it.

Below is the current macro for the STATS button:

Private Sub CommandButton16_Click() 'STATS button
Dim buttoncaption As String
buttoncaption = CommandButton16.Caption 'Caption = STATS
Application.Goto Reference:=Worksheets(buttoncaption).Range("A1")
End Sub

Thanks in advance.

 

[Billy Liddel]

Assuming that 2004 in the stats sheet is in column B then something like this:

Sub GotoStats()

Dim iShtNo As Integer
Dim Addr As String

iShtNo = ActiveSheet.Index + 1
Addr = Cells(1, iShtNo).Address
Application.Goto Reference:=Sheets("STATS").Range(Addr)

End Sub

Regards
Peter Atherton

 

[WLMPilot]

I have not tried your suggestion. But it does not look like what I am
looking for. First of all, I indicated that the YEAR (2004-2009) are in
column A on the STATS sheet.

When I am on a worksheet (other than STATS, ie 2004, 2005...2009) the YEAR
is in cell A1. I am figuring that the macro would look at A1 to get the
year, then switch to STATS and locate that year in column A so that I would
be taken directly to the stats for the worksheet I was just on.

Les

 

[JLGWhiz]

Les, this assumes that your command buttons are from the Control
Toolbox. If they are not then you will need to retitle the
macro and put it in the standard code module instead of the control's
code module. Otherwise, just right click each of your command buttons
and paste this code into the code module.

Private Sub CommandButton1_Click() 'change button name to suit.
lr = Sheets("STATS").Cells(Rows.Count, 1).End(xlUp).Row
myStat = ActiveSheet.Range("A1").Value
Set c = Worksheets("STATS").Range("A2:A" & lr).Find(myStat,
Lookin:=xlValues)
If Not c is Nothing Then
Sheets("STATS").Activate
c.EntireRow.Select
End If
End sub