Macro to copy and paste into a custom filter

[Guest]

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

 

[Debra Dalgleish]

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.

 

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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

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

 

[Gord Dibben]

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

 

[Guest]

Call me greedy, but here's an addition

If I want the value to remain in the clip board so i can paste it elsewhere
after the macro has been run. I tried this but it didn't work, as I said I'm
a VB noob

Sub Macro1()

Selection.Copy

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

Any thoughts anyone?