400 Error on Macro

[tlc_in_OK]

I have two macros, one is working fine and the other generates an error, and I can't figure out why. They both take filtered pivot table data and paste it to other columns (which we use to do calculations and a graph).

The first one changes the filter to "Accepted" status, and copies A6:Cx over to F2:Hx. Here's the code:

Sub CopyPivotInfo1()
Dim RngPS As Range, RngPCR As Range, Rng As Range, Last As Long
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = True
.PivotItems("No Bid-No Sale").Visible = False
.PivotItems("Rejected").Visible = False
With Sheets("Pivot")
Set RngPS = .Range(.Range("A6"), .Range("C" & Rows.Count).End(xlUp))
End With
With Sheets("Pivot")
Set Rng = .Range(.Range("F$2"), .Range("H" & Rows.Count).End(xlUp))
Rng.Resize(, 3).ClearContents
RngPS.Copy .Range("F2")
'Last = .Range("H" & Rows.Count).End(xlUp).Row
End With

End With
End Sub
​

This second one simply changes the filter on the pivot, selects the same cells as the sub above and should paste it to the next blank row in F (although I'm not sure I have the range right), but I keep getting a "400" error when I try to run it. Any ideas?

Sub CopyPivotInfo4()
Dim RngPS As Range
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = False
.PivotItems("No Bid-No Sale").Visible = True
.PivotItems("Rejected").Visible = True

Set RngPS = .Range(.Range("A6"), .Range("C" & Rows.Count).End(xlUp))
RngPS.Copy .Range("F" & Cells(Rows.Count, 6).End(xlUp).Offset(1, 0))

End With
End Sub
​

 

[tlc_in_OK]

I can hard code "F129" into the "paste" range in the last line of my code, and it works fine. There's something about this (which should result to F129 right now):

.Range("F" & Cells(Rows.Count, 6).End(xlUp).Offset(1, 0))

that it doesn't like. Any ideas?

 

[pbornemeier]

.Range("F" & Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row)

 

[tlc_in_OK]

Thanks Phil. . .I knew it was something simple. Guess I had just stared at it for too long!