Trouble copying data based on contents of a cell

[althea]

I have the following code, but I'm not getting the correct results. In a list on Sheet1, I have one column that shows month/year (column P). In cell A1 of the same sheet, there is a month/year value. If any data in column P equals the same month/year, I'd like the entire row of data in the list copied to Sheet2 in a certain position, let's say starting at row 187. My problem is that with this code, when I run it I only get the first two rows in my list copied to the other sheet. Anyone know what I'm doing wrong?

Sub CopyData()
Dim rngRow As Range, rngCol As Range, rngX As Range, ws As Worksheet, cell As CellFormat
Set ws = Worksheets("Sheet2&quot
lRow = 187
With Worksheets("Sheet1&quot
Set rngX = Range(.Cells(11, 16), .Cells(11, 16).End(xlDown))
Set rngRow = Range(.Cells(10, 1), .Cells(10, 1).End(xlDown))
Set rngCol = Range(.Cells(10, 1), .Cells(10, 1).End(xlToRight))
For Each r In rngX
If r.Value = Range("a1&quot.Value Then

For Each c In rngCol
ws.Cells(lRow, c.Column).Value = .Cells(r.Row, c.Column).Value
Next
lRow = lRow + 1
End If
Next
End With
End Sub

 

[JohnCHolmes]

Have you stepped through the code? Use the debugger. That can't possibly be less trouble than getting the solution third-hand.

One of the things that debugging does is causes you to question exactly what you've ordered the code to do. Your code says "go end-down" (etal). Have you examined what that does in the actual sheet?

 

[althea]

Thanks! Good idea. I'll try to use the debugger.

 

[JohnCHolmes]

Well garsh I took for granted you knew how. IMHO you really ought to have that skillset to mess with VBA.

As a quick start, go tools/macro/macros, highlight yours, click Step Into. Then use F8 to step through the code. Pass your mouse over code variables to see current contents (for _some_ variables and properties). Within VBA, Run/reset to stop execution (or alt-F4).

Those are the basics; you can get far more elaborate, though for your small amount of code and variables there's not so great a need for more than what I've described. You might want to poke around, though, because the debugger can potentially (and sometimes does) make one many times more productive in solving code problems.

 

[althea]

Thanks for the tips. Yes, I have used the debugger many times. I didn't find it helpful for this because it just tells me exactly when the data gets copied onto the next sheet. I need to know why I can't copy only the data that matches the contents of A1.