How do I use a cell value as a parameter

[Richhall]

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

 

[Chip Pearson]

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)

 

[Don Guillett]

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

 

[Richhall]

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)









- Show quoted text -

Excellent, thank you both. Cheers, Rich

 

[Don Guillett]

When I posted, Chips answer was NOT visible to me

 

[Chip Pearson]

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)