Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.

Anyone able to offer some advice?

Thanks,
Jack

If you give a bit more detail on what you're trying to filter, someone
may be able to help. If you recorded some code, you could include that,
and describe what happened (or didn't happen) when you tried to run the
recorded macro.

Jtmturner wrote:
Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Oh, ok, well when I record a macro to do what i want this code is produced,

Sub Macro1()
ActiveCell.Select
Selection.Copy
Sheets("Payments").Select
Selection.AutoFilter Field:=4, Criteria1:="=1005960", Operator:=xlAnd
End Sub

I'm copying a number from a cell on a worksheet called Discrepancies, then
pasting that value into the custom auto filter on a worksheet called
Payments. From my understanding of the code, the Criteria is wrong and
instead of being the number I copied, it should actually be the contents of
the clip board, whatever they may be at that moment.

On the sheet 'discrepancies' I have a list of numbers( among other things),
and for each number I need to look up Payment info on it on the 'payments'
sheet by applying a custom filter to column D.

What would be really really nice is if I selected the cell containing the
number I want on 'discrepancies' and just hit a single buttong "ctrl
whatever" and it carried out the whole process for me :)

for example with the desired cell is selected/highlighted I start the macro
it copies the active cell contents
changes to the payments sheet
applies a custom filter with the criteria of the contents of the clipboard

That would seriously save me alot of time, and probably save from getting
RSI too :)

Basically I need to know the commands for switching worksheets that I'm
viewing and then the function to paste the contents of the clip board rather
than a specific number.

Thanks for your response and help,

Jack
"Debra Dalgleish" wrote:

If you give a bit more detail on what you're trying to filter, someone
may be able to help. If you recorded some code, you could include that,
and describe what happened (or didn't happen) when you tried to run the
recorded macro.

Jtmturner wrote:
Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Try this on for size.

Select your cell with value you want then run.

Sub Filter_Stuff()
Dim whatwant As String
Dim collett As Integer
whatwant = ActiveCell.Value
Sheets("Payments").Select
'for picking a column to sort on if you wish
'collett = InputBox("Enter the column number")
'change the Field:=4 to Field:=collett
With ActiveSheet.UsedRange
Selection.AutoFilter
Selection.AutoFilter Field:=4, _
Criteria1:=whatwant, Operator:=xlAnd
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 14:49:01 -0800, Jtmturner
wrote:

Oh, ok, well when I record a macro to do what i want this code is produced,

Sub Macro1()
ActiveCell.Select
Selection.Copy
Sheets("Payments").Select
Selection.AutoFilter Field:=4, Criteria1:="=1005960", Operator:=xlAnd
End Sub

I'm copying a number from a cell on a worksheet called Discrepancies, then
pasting that value into the custom auto filter on a worksheet called
Payments. From my understanding of the code, the Criteria is wrong and
instead of being the number I copied, it should actually be the contents of
the clip board, whatever they may be at that moment.

On the sheet 'discrepancies' I have a list of numbers( among other things),
and for each number I need to look up Payment info on it on the 'payments'
sheet by applying a custom filter to column D.

What would be really really nice is if I selected the cell containing the
number I want on 'discrepancies' and just hit a single buttong "ctrl
whatever" and it carried out the whole process for me :)

for example with the desired cell is selected/highlighted I start the macro
it copies the active cell contents
changes to the payments sheet
applies a custom filter with the criteria of the contents of the clipboard

That would seriously save me alot of time, and probably save from getting
RSI too :)

Basically I need to know the commands for switching worksheets that I'm
viewing and then the function to paste the contents of the clip board rather
than a specific number.

Thanks for your response and help,

Jack
"Debra Dalgleish" wrote:

If you give a bit more detail on what you're trying to filter, someone
may be able to help. If you recorded some code, you could include that,
and describe what happened (or didn't happen) when you tried to run the
recorded macro.

Jtmturner wrote:
Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I think.....yup, I'm in love with you.

Thank you thank you thank you!!!!!!!!

"Gord Dibben" wrote:

Try this on for size.

Select your cell with value you want then run.

Sub Filter_Stuff()
Dim whatwant As String
Dim collett As Integer
whatwant = ActiveCell.Value
Sheets("Payments").Select
'for picking a column to sort on if you wish
'collett = InputBox("Enter the column number")
'change the Field:=4 to Field:=collett
With ActiveSheet.UsedRange
Selection.AutoFilter
Selection.AutoFilter Field:=4, _
Criteria1:=whatwant, Operator:=xlAnd
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 14:49:01 -0800, Jtmturner
wrote:

Oh, ok, well when I record a macro to do what i want this code is produced,

Sub Macro1()
ActiveCell.Select
Selection.Copy
Sheets("Payments").Select
Selection.AutoFilter Field:=4, Criteria1:="=1005960", Operator:=xlAnd
End Sub

I'm copying a number from a cell on a worksheet called Discrepancies, then
pasting that value into the custom auto filter on a worksheet called
Payments. From my understanding of the code, the Criteria is wrong and
instead of being the number I copied, it should actually be the contents of
the clip board, whatever they may be at that moment.

On the sheet 'discrepancies' I have a list of numbers( among other things),
and for each number I need to look up Payment info on it on the 'payments'
sheet by applying a custom filter to column D.

What would be really really nice is if I selected the cell containing the
number I want on 'discrepancies' and just hit a single buttong "ctrl
whatever" and it carried out the whole process for me :)

for example with the desired cell is selected/highlighted I start the macro
it copies the active cell contents
changes to the payments sheet
applies a custom filter with the criteria of the contents of the clipboard

That would seriously save me alot of time, and probably save from getting
RSI too :)

Basically I need to know the commands for switching worksheets that I'm
viewing and then the function to paste the contents of the clip board rather
than a specific number.

Thanks for your response and help,

Jack
"Debra Dalgleish" wrote:

If you give a bit more detail on what you're trying to filter, someone
may be able to help. If you recorded some code, you could include that,
and describe what happened (or didn't happen) when you tried to run the
recorded macro.

Jtmturner wrote:
Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome.

Thanks for the feedback.

BTW.......Mrs. Dibben wants to know how soon you'll be taking me off her hands,
please.


Gord

On Wed, 31 Jan 2007 17:12:01 -0800, Jtmturner
wrote:

I think.....yup, I'm in love with you.

Thank you thank you thank you!!!!!!!!

"Gord Dibben" wrote:

Try this on for size.

Select your cell with value you want then run.

Sub Filter_Stuff()
Dim whatwant As String
Dim collett As Integer
whatwant = ActiveCell.Value
Sheets("Payments").Select
'for picking a column to sort on if you wish
'collett = InputBox("Enter the column number")
'change the Field:=4 to Field:=collett
With ActiveSheet.UsedRange
Selection.AutoFilter
Selection.AutoFilter Field:=4, _
Criteria1:=whatwant, Operator:=xlAnd
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 14:49:01 -0800, Jtmturner
wrote:

Oh, ok, well when I record a macro to do what i want this code is produced,

Sub Macro1()
ActiveCell.Select
Selection.Copy
Sheets("Payments").Select
Selection.AutoFilter Field:=4, Criteria1:="=1005960", Operator:=xlAnd
End Sub

I'm copying a number from a cell on a worksheet called Discrepancies, then
pasting that value into the custom auto filter on a worksheet called
Payments. From my understanding of the code, the Criteria is wrong and
instead of being the number I copied, it should actually be the contents of
the clip board, whatever they may be at that moment.

On the sheet 'discrepancies' I have a list of numbers( among other things),
and for each number I need to look up Payment info on it on the 'payments'
sheet by applying a custom filter to column D.

What would be really really nice is if I selected the cell containing the
number I want on 'discrepancies' and just hit a single buttong "ctrl
whatever" and it carried out the whole process for me :)

for example with the desired cell is selected/highlighted I start the macro
it copies the active cell contents
changes to the payments sheet
applies a custom filter with the criteria of the contents of the clipboard

That would seriously save me alot of time, and probably save from getting
RSI too :)

Basically I need to know the commands for switching worksheets that I'm
viewing and then the function to paste the contents of the clip board rather
than a specific number.

Thanks for your response and help,

Jack
"Debra Dalgleish" wrote:

If you give a bit more detail on what you're trying to filter, someone
may be able to help. If you recorded some code, you could include that,
and describe what happened (or didn't happen) when you tried to run the
recorded macro.

Jtmturner wrote:
Hi there,
I'm copying the contents of a cell I select from one worksheet and pasting
that value into the "custom" selection of the auto filter on a different
worksheet to produce information on that value.

Anyway, I have to do this about a thousand times, and tried to make a macro
to help speed up the process, but I failed, I'm pretty novice with macros and
VB.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html