Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?


Many, many thanks if you can help with this.

Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.

Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson

That works. Thanks Dave. Now there is one other thing that is not
essential, but will improve things even further. Is there a way of
looking at two cell ranges in the criteria instead of just the one.

To put it another way, in the same way you can go =E8&" "&F8 in a cell
- is something like that possible for the purpose of criteria in the
VB code?

I have no idea how this would be written - whether it would mean
putting in a AND command (if there is such a thing) or if it could be
included somehow in the range section.

Thanks.


On 8 Aug, 15:56, Dave Peterson wrote:
Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.





Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Just concatenate your strings (I think):

Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value & " " _
worksheets("adifferent").range("x999").value




Midnight wrote:

That works. Thanks Dave. Now there is one other thing that is not
essential, but will improve things even further. Is there a way of
looking at two cell ranges in the criteria instead of just the one.

To put it another way, in the same way you can go =E8&" "&F8 in a cell
- is something like that possible for the purpose of criteria in the
VB code?

I have no idea how this would be written - whether it would mean
putting in a AND command (if there is such a thing) or if it could be
included somehow in the range section.

Thanks.

On 8 Aug, 15:56, Dave Peterson wrote:
Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.





Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Hi Dave,

The code worked good but I had to take out the " " _ - I can see
why you put that in, and it's exactly what I need (to have a space
between the two values) but VB didn't allow it. Is there another way
you can think of doing it? I tried taking out the underscore but no
joy.

As a workaround, I can add a space to the end of the text string of
the first cell value (at the source), but it would be better if I
could incorporate the space into the code.

Jon.


On 8 Aug, 18:23, Dave Peterson wrote:
Just concatenate your strings (I think):

Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value & " " _
worksheets("adifferent").range("x999").value





Midnight wrote:

That works. Thanks Dave. Now there is one other thing that is not
essential, but will improve things even further. Is there a way of
looking at two cell ranges in the criteria instead of just the one.

To put it another way, in the same way you can go =E8&" "&F8 in a cell
- is something like that possible for the purpose of criteria in the
VB code?

I have no idea how this would be written - whether it would mean
putting in a AND command (if there is such a thing) or if it could be
included somehow in the range section.

Thanks.

On 8 Aug, 15:56, Dave Peterson wrote:
Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.

Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I omitted an ampersand.

Criteria1:=worksheets("othersheet").range("a177"). value & " " _
& worksheets("adifferent").range("x999").value

Sorry.

Midnight wrote:

Hi Dave,

The code worked good but I had to take out the " " _ - I can see
why you put that in, and it's exactly what I need (to have a space
between the two values) but VB didn't allow it. Is there another way
you can think of doing it? I tried taking out the underscore but no
joy.

As a workaround, I can add a space to the end of the text string of
the first cell value (at the source), but it would be better if I
could incorporate the space into the code.

Jon.

On 8 Aug, 18:23, Dave Peterson wrote:
Just concatenate your strings (I think):

Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value & " " _
worksheets("adifferent").range("x999").value





Midnight wrote:

That works. Thanks Dave. Now there is one other thing that is not
essential, but will improve things even further. Is there a way of
looking at two cell ranges in the criteria instead of just the one.

To put it another way, in the same way you can go =E8&" "&F8 in a cell
- is something like that possible for the purpose of criteria in the
VB code?

I have no idea how this would be written - whether it would mean
putting in a AND command (if there is such a thing) or if it could be
included somehow in the range section.

Thanks.

On 8 Aug, 15:56, Dave Peterson wrote:
Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.

Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

On 9 Aug, 13:10, Dave Peterson wrote:
I omitted an ampersand.

Criteria1:=worksheets("othersheet").range("a177"). value & " " _
& worksheets("adifferent").range("x999").value

Sorry.





Midnight wrote:

Hi Dave,

The code worked good but I had to take out the " " _ - I can see
why you put that in, and it's exactly what I need (to have a space
between the two values) but VB didn't allow it. Is there another way
you can think of doing it? I tried taking out the underscore but no
joy.

As a workaround, I can add a space to the end of the text string of
the first cell value (at the source), but it would be better if I
could incorporate the space into the code.

Jon.

On 8 Aug, 18:23, Dave Peterson wrote:
Just concatenate your strings (I think):

Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value & " " _
worksheets("adifferent").range("x999").value

Midnight wrote:

That works. Thanks Dave. Now there is one other thing that is not
essential, but will improve things even further. Is there a way of
looking at two cell ranges in the criteria instead of just the one.

To put it another way, in the same way you can go =E8&" "&F8 in a cell
- is something like that possible for the purpose of criteria in the
VB code?

I have no idea how this would be written - whether it would mean
putting in a AND command (if there is such a thing) or if it could be
included somehow in the range section.

Thanks.

On 8 Aug, 15:56, Dave Peterson wrote:
Selection.AutoFilter Field:=1, _
Criteria1:=worksheets("othersheet").range("a177"). value

may work ok.

Midnight wrote:

Hi,

I have a simple bit of code to take me to another worksheet and
automatically select specific information falling under a autofilter.
Sample below:

Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="RandomText"
Range("A1").Select

This works great but it would be even better if I could refer to a
cell within the spreadsheet. How do I go about changing the criteria
to be a cell reference?

Since I have multiple sheets, do I need to specify the sheet name that
I'm calling the cell reference from?

Many, many thanks if you can help with this.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for your help Dave. It now works just as I wanted.