Copy range of cells from one worksheet to another if cell has a specific valule

[garbour]

Hey all,

I have a simple VBA string of code that should look at a cell range and if the cell = 0 copy that row and past it into another sheet. If the cell does not = 0 the code should skip that row (don't copy anything) and move on to the next one. Here is my current code with some notes in red.

What should I be using for this line of code?

Sub Evaluate_Data()


'Declare variables
Dim intStartRow As Long
Dim intEndRow As Long
Dim intTargetColumn As Long
Dim intCounter As Integer

'set default values
intStartRow = Range("qty_range").Row
intEndRow = Range("qty_range").Row + Range("qty_range").Rows.Count - 1
intTargetColumn = Range("qty_range").Column

For intCounter = intStartRow To intEndRow
'if the cell contains a zero value
If Len(Trim(Cells(intCounter, intTargetColumn).Value)) = 0 Or Cells(intCounter, intTargetColumn).Value = 0 Then
' paste the value

[this line works, but it pastes every cell in the range] Range("qty_range").Copy

[what I want it to do is similar to my code to hide rows (see below) but instead paste the values)

Rows(intCounter & ":" & intCounter).EntireRow.Hidden = True]

Sheet3.Range("D1").PasteSpecial Paste:=xlPasteValues


End If
Next intCounter
End Sub


Thanks,

garbour

 

[garbour]

Hey Jerry,

Wanted to let you know that I followed your advice and qualified all of my references with the applicable sheet references and got the code to work! Thanks for your help debugging this with me. For anyone that might ever need to do this I have pasted the code that works below.

Option Explicit
Sub Evaluate_Data()

'Declare variables
Dim intStartRow As Long
Dim intEndRow As Long
Dim intTargetColumn As Long
Dim intCounter As Integer, intCounter2 As Integer


'set default values
intStartRow = Sheet2.Range("qty_range").Row
intEndRow = Sheet2.Range("qty_range").Row + Sheet2.Range("qty_range").Rows.Count - 1
intTargetColumn = Sheet2.Range("qty_range").Column

For intCounter = intStartRow To intEndRow
'if the cell contains a zero value
If Sheet2.Cells(intCounter, intTargetColumn).Value = 0 Then
'copy the value
Sheet2.Range("qty_range").Resize(1).Offset(intCounter - intStartRow).EntireRow.Copy
' paste the value
intCounter2 = intCounter2 + 1
Sheet3.Range("A8").Offset(intCounter2, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Application.CutCopyMode = False
End If


Next intCounter
End Sub


garbour