VBA - Select, copy, paste entire row based on criteria

[seanjon]

I have search, so forgive me if I just could not find it.

Sheet "DATA" contains all the raw data
Sheet "RATE" is where I want 'rated' jobs
Sheet "HOT" is where I want all the 'hot' jobs
Everything with an R at the end of the work center number in Column A is considered "RATED"
Everything with the word 'HOT' in column B is considered "HOT"

I need the raw data to stay on the "DATA" sheet it is on as I use it to sort data for another reason.

I need a vba code that will select all the rows with rate work centers, copy the entire row, and paste them to the sheet "RATE" and code that will select all the rows with hot jobs, copy the entire row, and paste them to the sheet "HOT"

Thanks in advance
SJ

 

[Fluff]

Hi
Do you have a header in row1 with data starting in row2?
Also do you want to clear the Rate & Hot sheets, prior to copying the new data, or just add the new data at the bottom?

 

[seanjon]

Hi! Thanks for the quick reply!

Data starts at row 6.

Yes, I would like to clear the data in the other sheets prior to pasting the new data.

SJ

 

[Fluff]

Ok, try this

Sub FilterCopy()

   Sheets("Rate").UsedRange.Offset(1).Clear
   Sheets("Hot").UsedRange.Offset(1).Clear
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A5").AutoFilter 1, "*R"
      On Error Resume Next
      .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("Rate").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("A5").AutoFilter
      .Range("A5").AutoFilter 2, "Hot"
      .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("Hot").Range("A" & Rows.Count).End(xlUp).Offset(1)
      On Error GoTo 0
      .AutoFilterMode = False
   End With
End Sub

 

[seanjon]

Fluff,

Works perfectly! You da bomb!

Thank you very much!

SJ

 

[Fluff]

Glad to help & thanks for the feedback

 

[Enzo_Matrix]

Hi Fluff,

I like this code but I'm hoping you can help me modifying it for my workbook.

- I want to add the data to the bottom of me sheet after it is copied
-Criteria for search are the words: "Weld","Comp","Rubber".
- my table has headers
- data starts in cell A2
- sheet names are: Data, Weld, Comp, Rubber

Anything you can do would be appreciated.

 

[Fluff]

Untested, but try

Sub FilterCopy()
   
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Weld", "Comp", "Rubber")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Ary)
         .Range("A1").AutoFilter[COLOR=#ff0000] 1[/COLOR], Ary(i)
         On Error Resume Next
         .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets(Ary(i)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         On Error GoTo 0
      Next i
      .AutoFilterMode = False
   End With
End Sub

Change field number in red to match the column containing your values

 

[Enzo_Matrix]

Thanks a lot Fluff.

I just realized that the data should have been cleared each time the copy code is run. Is there an easy way to modify this to accomplish that?

Sub FilterCopy()   
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Weld", "Composite", "Rubber")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Ary)
         .Range("A4").AutoFilter 1, Ary(i)
         On Error Resume Next
         .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets(Ary(i)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         On Error GoTo 0
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[Fluff]

How about

Sub FilterCopy()
   
   Dim Ary As Variant
   Dim i As Long
   Dim Sht As Variant
   
   Ary = Array("Weld", "Comp", "Rubber")
   For Each Sht In Ary
      Sheets(Sht).UsedRange.Clear
   Next Sht
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Ary)
         .Range("A1").AutoFilter 1, Ary(i)
         On Error Resume Next
         .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets(Ary(i)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         On Error GoTo 0
      Next i
      .AutoFilterMode = False
   End With
End Sub