I'm attempting to copy all rows from sheet "meowmix" that meet certain requirements paste those rows to sheet "woofmix".
Ideally, I would like overwrite all row on "woofmix" ,except for the first row, with the copied rows from "meowmix".
The below code successfully identifies rows with requirements and pastes them to the target sheet, however, it adds on to the target sheet rather than replacing. I'm not having an issue with suppressing the 'overwrite destination cells' dialogue box. I'm having and issue overwriting.
Sub replacecurrentrows()
Dim rngRow As Range, MEOW As Range, lngrow As Long
Dim varCheckCol1 As Variant, varCheckCol2 As Variant, varCheckCol3 As Variant
Set MEOW = Sheets("meowmix").Range("O2:P5000")
For Each rngRow In MEOW.Rows
varCheckCol1 = rngRow.Value2(1, 1) 'col O
varCheckCol2 = rngRow.Value2(1, 2) 'col P
'varCheckCol3 = rngRow.Value2(1, 5) 'col Q
If varCheckCol1 = "Kibbles" And varCheckCol2 = "Bits" Then
If rngRow Is Nothing Then
rngRow.EntireRow.Copy
Sheets("Woofmix").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
End Sub
Bookmarks