How to select a sheet and input data into certain cells

[Guest]

I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum

AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT
AA2=2004 Sheet2 Sheet3 etc . . .
AA3=2005 Sheet6 Sheet
AA4=2006 Sheet8 Sheet
AA5=2007 Sheet4 Sheet

I would like to be able to select a year and a quarter and it goes to the sheet, i.e
2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter
I would also like to be able to print sheet5 and ask me if I want to print it agai
or close and upon closing go back to sheet

Is there an example that I might be able to look at that might lead me in th
direction that I need to go

Thank yo

 

[Frank Kabel]

Hi
not really sure but for creating a list of sheets with hyperlinks see:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Guest]

A GREAT site, but way over my head

Thank you

 

[Bob Phillips]

Here is one way,

First select all of the data, including the year and qtr headings and name
that selection 'data' . To do this, select the data as I say, and in the
names box (the little dropdown box to the left of the formula bar) type
'data' (without the quotes).

Then create year data validation in another cell, I use Z1. Select Z1, goto
menu Data>Data Validation. Change the 'Allow' dropdown to 'List', select the
'Source' edit box and then select your year headings (AA2:AAn where n is the
last row). DV will automatically change this to =$AA$2:$AA$n. Exit DV.

Then create a quarters data validation in say Z2.goto menu Data>Data
Validation. Change the 'Allow' dropdown to 'List', select the 'Source' edit
box and then select your quarters headings (AA1:Am1 where m is the last
column). DV will automatically change this to =$AA$1:$Am$1. Exit DV.

Now add this code into the worksheet module (right-click on the sheet tab,
select View Code from the menu, and paste the code in)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sSheet As String
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Z2")) Is Nothing Then
With Target
If .Value <> "" Then
If Me.Range("Z1") <> "" Then
With Application
sSheet = .Index(Range("data"), _
.Match(Range("Z1"), Range("AA:AA"), 0), _
.Match(Range("Z2"), Range("AA1:AZ1"), 0))
End With
If sSheet <> "" Then
Worksheets(sSheet).Activate
End If
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Quit the VBE and go back to the worksheet.

Now if you select Z1 you will see a dropdown box. Select your year from
this. Similarly, in Z2 you will see another dropdown box, select your
quarter from this. If all has worked okay, you should go to your target
sheet.

There is error checking, so if a sheet entry is blank, or the year or
quarter DV cells are blank nothing happens. This is how you can force it to
go to a sheet that is already selected. For example, Z1 = 2004, Z2 = 3rdQtr,
and that is where you want to go. Clear Z2, the re-select 3rdQtr from the
dropdown.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[RK]

I selected the data, but it doesn't save "data" (without
quotes) in the name box.

RK