Transfer data from one worksheet to another

[JayB0730]

Hello,

I have the following vba code that works to a degree; however, I want to make a few changes.

1. Instead of the logic looking for any rows with data in column 37, I want it to find specific values (e.g. "Apple") instead.
2. Paste the information into the new sheet without any formatting.

Sub SelectDestruct()   Dim Ar As Areas
   Dim Rng As Range
   
   Sheets("Destination_Sheet").UsedRange.Offset(1).Clear
   On Error Resume Next
   Set Ar = Sheets("Origin_Sheet").Columns(37).SpecialCells(xlCellTypeConstants, xlTextValues).Areas
   On Error GoTo 0
   If Ar Is Nothing Then
      MsgBox "Nothing to do!"
      Exit Sub
   End If
   For Each Rng In Ar
      Rng.Offset(, -35).Resize(, 12).Copy Sheets("Destination_Sheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next Rng
Call Confirmation
End Sub

TIA!

Jay

 

[Fluff]

Untested, but try

Sub SelectDestruct()
   
   Sheets("Destination_Sheet").UsedRange.Offset(1).Clear
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1").UsedRange.AutoFilter 37, "Apple"
      .AutoFilter.Range.Offset(1).Copy
      Sheets("Destination_Sheet").Range("A" & Rows.Count).End(xlUp).Offset (1)
      .AutoFilterMode = False
   End With
Call Confirmation
End Sub