Search criteria, cut, and paste in new worksheet

[JJBPSU]

I am new to VBA and stuck!!

I'm trying to write code to search for multiple values (total of 31 different values) from column "T" in "worksheet 5". Two example of multiple values include, "22400" and "22411".

I want to take each value found from search== cut the entire row for each value found and paste into new worksheet.

I'm stuck with writing code for more than 1 value.

[Aussiebear]

What code do you have so far?

[JJBPSU]

Hi there- here is what I have so far. This is my first macro project that isn't "basic".


[vba]Application.ScreenUpdating = False
'Worksheets("Raw Data").Activate
'FilterCriteriaBF = ActiveSheet.Range.Value2
'Range("FilterKey") = FilterCriteriaBF
'Clear Contents
'Sheets("Raw Data").Select
'Range("A1:AJ" & LastRow).Select
'Selection.ClearContents
'Range("A1").Activate
Sheets("Raw Data").Range("A1:AJ" & lastrow).ClearContents
'AutoFilter
Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =OR("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter Field:=20, Criteria1:=True
'Copy
'Worksheets("Raw Data").Select
'Range("A1:AJ" & LastRow).Select
'Selection.Copy
'Worksheets("ExclFellowInternResidentSumYth").Select
'Range("A1").Select
'ActiveSheet.Paste
Worksheets("Raw Data").Range("A1:AJ" & lastrow).Copy Destination:=Worksheets("FilterData").Range("A1")
'AutoFilter Off
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter
'Sheets("ExclFellowInternResidentSumYth").Select
'Range("H6").Select
'Selection = 1
Sheets("ExclFellowInternResidentSumYth").Range("H6") = 1
Application.ScreenUpdating = True[/vba]

[Aussiebear]

Okay, just a couple of quick hints firstly

1. 8 lines out of 29 presented lines are only the actual working section of code. Makes it hard reading.

2. Be cautious when using macro recoded code.

Even though its commented out, things such as
[vba]
Worksheets("Raw Data").Select
Range("A1:AJ") & LastRow).Select
Selection.Copy
[/vba]
Can be better written, such as
[vba]

With Worksheets("Raw Data")
.Range("A:AJ") & LastRow.Copy
[/vba]
And while we are talking about commenting code out, a single apostrophe is all that is required. I amended your presented code to make it more readable.

This the actual working section of the code you have provided.
[vba]
Application.ScreenUpdating = False
Sheets("Raw Data").Range("A1:AJ" & lastrow).ClearContents
Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =Or("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter Field:=20, Criteria1:=True
Worksheets("Raw Data").Range("A1:AJ" & lastrow).Copy Destination:=Worksheets("FilterData").Range("A1")
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter
Sheets("ExclFellowInternResidentSumYth").Range("H6") = 1
Application.ScreenUpdating = True
[/vba]


When writing formulas in vba, the following won't work
[vba] Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =Or("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"
[/vba]
because you are missing the double quotation in front of the second equals sign. This may be just a typo but you need to check in your actual code.

Now we get down to the actual logic of the code.
You attempt to clear contents of the range A1to AJ1 & down to the last row, then replace it with an "or" formula. How does this work?