Copy row contents based on contents
 

I'm working on a macro to automagically copy the entire row of a worksheet
(Sheet 1) to Sheet 2 if the cell in column D contains the word "total". I
got this to work once but then I took the code and tried using it for a
different worksheet. Once I did this it no longer worked. There must be
some minutia that I'm missing. Can someone please help me. Thank you.
Here's the code I have so far:

Sub CopyTotalsWithAutofilter()
Dim FilterValue As String
Dim rng As Range
Dim rng2 As Range

Set rng = Sheets("Sheet1").Range("D:D")
FilterValue = "*total*"
rng.AutoFilter Field:=1, Criteria1:=FilterValue
With Sheets("Sheet1").AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Copy Sheets("Sheet2").Range("A1")
End If
End With
ActiveSheet.AutoFilterMode = False
End Sub

Thanks!!

Copy row contents based on contents
 

Your code is pretty specific in a couple places.

Did you change all the references to Sheet1 and sheet2 to what you needed for
that other worksheet?

In fact, you may want to remove the Worksheets("Sheet1") reference and just
change it to activesheet. But I'm not sure how you could be so generic for the
"receiving" worksheet name.

naterator wrote:

I'm working on a macro to automagically copy the entire row of a worksheet
(Sheet 1) to Sheet 2 if the cell in column D contains the word "total". I
got this to work once but then I took the code and tried using it for a
different worksheet. Once I did this it no longer worked. There must be
some minutia that I'm missing. Can someone please help me. Thank you.
Here's the code I have so far:

Sub CopyTotalsWithAutofilter()
Dim FilterValue As String
Dim rng As Range
Dim rng2 As Range

Set rng = Sheets("Sheet1").Range("D:D")
FilterValue = "*total*"
rng.AutoFilter Field:=1, Criteria1:=FilterValue
With Sheets("Sheet1").AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Copy Sheets("Sheet2").Range("A1")
End If
End With
ActiveSheet.AutoFilterMode = False
End Sub

Thanks!!

--

Dave Peterson