Help with code

[WLMPilot]

I have a workbook with several worksheets --> STATS, 2005, 2006,...2009

The STATS worksheet has stat info for each year worksheet. The info in
STATS is displayed vertically with the year indicated in column A

I have a commandbutton in each year worksheet (2005 - 2009) with caption
STATS. Also in each year worksheet, the year (for that worksheet) is in
cell A1.

I would like a macro for the STATS commandbutton that will get the year in
cell A1, look in the STATS worksheet in column A and find that same year,
then goto that year so that I do not have to scroll down in STATS to find the
section pertaining to the year worksheet. Below is code that both I have
entered and have also obtained from this forum. However, I cannot get it to
work.

Private Sub CommandButton16_Click() 'STATS button
Dim buttoncaption As String
Dim yr As String
buttoncaption = CommandButton16.Caption
yr = Range("A1").Value
lr = Sheets("STATS").Cells(Rows.Count, 1).End(xlUp).Row
Application.Goto Reference:=Worksheets(buttoncaption).Range("A1")
Set c = Worksheets("STATS").Range("A2:A" & lr).Find(yr, LookIn:=xlValues)
If Not c Is Nothing Then
Sheets("STATS").Activate
c.EntireRow.Select
End If
End Sub

I appreciate any help with this!

Thanks,
Les

 

[JLGWhiz]

Hi Les, I don't understand what you are trying to do with the two lines that
you added:

buttoncaption = CommandButton16.Caption 'Add caption?

'Where does this sheet come from? Why?
Application.Goto Reference:=Worksheets(buttoncaption).Range("A1")

 

[WLMPilot]

JLG,

The buttoncaption was something I was playing around with learning how to
get the button caption.

Here is the original code I have for each STATS commandbutton that is found
on the worksheets whose name equals a year, ie 2004, 2005, 2006, 2007, 2008,
2009.

Private Sub CommandButton16_Click()
Application.Goto Reference:=Worksheets("STATS").Range("A1")
End Sub

The above code works and takes from Worksheet("2009"), for example, to the
STATS worksheet. Any code that is shown in initial question was a suggestion
made by someone in hopes it would do what I want.

Here is a rough layout of what the data on STATS worksheet looks like:

A B C D E ............. N
1 2005
2 JAN FEB MAR ......... DEC
3 Row Header data data data data
4 Row Header data data data data



60 2009
61 JAN FEB MAR ......... DEC
62 Row Header data data data data
63 Row Header data data data data

If I am in Worksheet("2009") and click the STATS commandbutton, I want the
macro to goto the STATS worksheet, find 2009 and place that year's data at
the top of the sheet.

Hope this helps.

Thanks,
Les

 

[JLGWhiz]

OK, Les, I think I have the picture now. Try the code below as is:

Private Sub CommandButton16_Click() 'STATS button

Dim yr As String
yr = ActiveSheet.Range("A1").Value
lr = Sheets("STATS").Cells(Rows.Count, 1).End(xlUp).Row
Set c = Worksheets("STATS").Range("A2:A" & lr).Find(yr, LookIn:=xlValues)
If Not c Is Nothing Then
Sheets("STATS").Activate
ActiveWindow.ScrollRow = c.Row
ActiveWindow.ScrollColumn = c.Column
End If
End Sub

This looks at cell A1 on the active sheet, goes to Sheets("STATS") and finds
that year value, then scrolls that found cell to the top left of the screen
in the STATS worksheet.

 

[WLMPilot]

I think you have the picture too, but it is not working. I click on the
STATS button but nothing happens.

I will keep testing different things. Don't know why it is not working.

Thanks,
Les

 

[JLGWhiz]

That particular macro was for the click event in Button 16, make sure your
title line is for the correct button. The easy way to do that is to just
copy everything between the title line and End Sub and then Paste it into the
default click event setup of each button. The actual code tested OK as far
as getting the year and bringing up the correct line on Worksheets("STATS").
The title line has to be the culprit.