Sheet reference in VBA
 

Hi,

In a macro I use

With Sheets("sht2").Range("b1:b100").AdvancedFilter _

to select a range on a sheet.

I want to select the sheet to perform this operation on by adding the sheet
name to a cell at a different sheet.
Say in the sheet "Sht1" in cell "a1" I put the name "Sht2"

How can I adjust teh code so that it will use at the value in sheet "Sht1"
in cell "a1"?

Best regards, Gijs

Sheet reference in VBA
 

Try

With
Sheets(sheets("Sht1").range("A1").text).Range("b1: b100").AdvancedFilter
_


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gijs Breedveld" wrote
in message
...
Hi,

In a macro I use

With Sheets("sht2").Range("b1:b100").AdvancedFilter _

to select a range on a sheet.

I want to select the sheet to perform this operation on by
adding the sheet
name to a cell at a different sheet.
Say in the sheet "Sht1" in cell "a1" I put the name "Sht2"

How can I adjust teh code so that it will use at the value in
sheet "Sht1"
in cell "a1"?

Best regards, Gijs

Sheet reference in VBA
 

Thanks it works!

Best regards,

Gijs

"Chip Pearson" wrote:

Try

With
Sheets(sheets("Sht1").range("A1").text).Range("b1: b100").AdvancedFilter
_


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gijs Breedveld" wrote
in message
...
Hi,

In a macro I use

With Sheets("sht2").Range("b1:b100").AdvancedFilter _

to select a range on a sheet.

I want to select the sheet to perform this operation on by
adding the sheet
name to a cell at a different sheet.
Say in the sheet "Sht1" in cell "a1" I put the name "Sht2"

How can I adjust teh code so that it will use at the value in
sheet "Sht1"
in cell "a1"?

Best regards, Gijs