Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

Sheets("Jan 08").Visible = True
Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich

Sub sheetvar()
Set myvar = Sheets(Sheets("sheet3").Range("b2").Value)
myvar.Visible = True
End Sub
However you do not have to make the sheet visible to do as desired

Sub FilterHiddenSheet()
Set myvar = Sheets(Sheets("sheet3").Range("e1").Value)
'myvar.Visible = True
myvar.Range("a1").AutoFilter Field:=1, Criteria1:=1
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Richhall" wrote in message
...
Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

Sheets("Jan 08").Visible = True
Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich

When I posted, Chips answer was NOT visible to me

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub sheetvar()
Set myvar = Sheets(Sheets("sheet3").Range("b2").Value)
myvar.Visible = True
End Sub
However you do not have to make the sheet visible to do as desired

Sub FilterHiddenSheet()
Set myvar = Sheets(Sheets("sheet3").Range("e1").Value)
'myvar.Visible = True
myvar.Range("a1").AutoFilter Field:=1, Criteria1:=1
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Richhall" wrote in message
...
Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

Sheets("Jan 08").Visible = True
Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich

When I posted, Chips answer was NOT visible to me

Server problems on my end, I think. The message sat in my OutBox for a long
time before making its way through the ether.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Don Guillett" wrote in message
...
When I posted, Chips answer was NOT visible to me

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub sheetvar()
Set myvar = Sheets(Sheets("sheet3").Range("b2").Value)
myvar.Visible = True
End Sub
However you do not have to make the sheet visible to do as desired

Sub FilterHiddenSheet()
Set myvar = Sheets(Sheets("sheet3").Range("e1").Value)
'myvar.Visible = True
myvar.Range("a1").AutoFilter Field:=1, Criteria1:=1
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Richhall" wrote in message
...
Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

Sheets("Jan 08").Visible = True
Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich

Rich,

You can use code like

Dim WS As Worksheet
Dim WSName As String
WSName = Worksheets("Sheet1").Range("B2").Value
Set WS = Worksheets(WSName)
WS.Select
WS.AutoFilter Field:=2, Criteria1:="Night"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Richhall" wrote in message
...
Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

Sheets("Jan 08").Visible = True
Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich

On Jan 6, 2:00*pm, "Chip Pearson" wrote:
Rich,

You can use code like

Dim WS As Worksheet
Dim WSName As String
WSName = Worksheets("Sheet1").Range("B2").Value
Set WS = Worksheets(WSName)
WS.Select
WS.AutoFilter Field:=2, Criteria1:="Night"

--
Cordially,
Chip Pearson
Microsoft MVP *- Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"Richhall" wrote in message

...



Hi, I have a value in a particular cell that I want to use to launch a
macro that will use that value. *I am not an expert on VB, but I guess
this means I could use the cell as a parameter?

The best way to explain is probably by showing the macro:

* *Sheets("Jan 08").Visible = True
* *Selection.AutoFilter Field:=2, Criteria1:="Night"

The Jan 08 value could change everytime, dependant on what is in the
cell (say B2) so it may May 08, Dec 08 etc. *I simply want the "Jan
08" to be "<cell B2" every time. Can anyone help please?

Cheers

Rich- Hide quoted text -

- Show quoted text -

Excellent, thank you both. Cheers, Rich