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
​

 

[JackDanIce]

Try the second one like:

Sub CopyPivotInfo4()
Dim RngPS As Range
With ActiveSheet
    With .PivotTables("PivotTable1").PivotFields("Report_Status")
        .PivotItems("Accepted").Visible = False
        .PivotItems("No Bid-No Sale").Visible = True
        .PivotItems("Rejected").Visible = True
    End With
    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

 

[pbornemeier]

I recommend you change the order of this block of code from

        .PivotItems("Accepted").Visible = False
        .PivotItems("No Bid-No Sale").Visible = True
        .PivotItems("Rejected").Visible = True

to

        .PivotItems("No Bid-No Sale").Visible = True
        .PivotItems("Accepted").Visible = False
        .PivotItems("Rejected").Visible = True

If the Accepted pivot item was the only one visible, then this line
.PivotItems("Accepted").Visible = False
in the first block would raise an error since at least one PivotItem must be visible.

 

[tlc_in_OK]

Thanks Guys, but it's still giving me the 400 error. Any other thoughts?

Changing the order of the pivot filters DID get it to finally filter the pivot, but for some reason, I'm still getting the 400 error. So it must be stemming from the copy/paste function. I can't see the error.

 

[pbornemeier]

What line of code shows the error? What is the text associated with Error 400?
Is row F (the insert point) inside the pivot table? Can you do manually what you are trying to do by code?

 

[tlc_in_OK]

It compiles, but simply gives me that horrid "400" error when I try to run it. When I try to step through it, it gives me a "Run-time error '1004': Application-defined or object-defined error" on the last "End With" statement.

BTW, "F" is NOT inside the pivot table, and I can do it manually, but I intend to hide this worksheet and have it all done in the background for end-users.

 

[pbornemeier]

If you turn on the macro recorder and perform the task that is failing manually what does the code show for that operation?

 

[pbornemeier]

Here is VBE Help on Error 400:

<TABLE id=topTable width="100%"><TBODY><TR id=headerTableRow2><TD align=left>Form already displayed; can't show modally (Error 400)</TD></TR></TBODY></TABLE>
You can't use the Show method to display a visible form as modal. This error has the following cause and solution:

• You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form. Use either the Unload statement or the Hide method on the form before trying to show it as a modal form.

Does it apply at all?

 

[tlc_in_OK]

No, doesn't apply.. .not using Show anywhere in the macro.

 

[tlc_in_OK]

If you turn on the macro recorder and perform the task that is failing manually what does the code show for that operation?

Here's what I get when I records the macro:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = False
End With
Range("A6:C74").Select
Selection.Copy
Range("F129").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
​

It doesn't include the "True" pivot selections at all. It selects the columns of filtered data in the pivot, and I copied the 3 columns in the pivot and pasted to the first blank cell in column "F" (happened to be 129 this time).