Object Missing?

[jski21]

Good day Mr. Excel Team,

Here's a snippet of code I'm using. Essentially trying to copy a pivot table and paste special the values in:

With ActiveSheet.PivotTables("DrawData").PivotFields("Years")
.Orientation = xlRowField
.Position = 2
Range("A6:F6").Select
Selection.Font.Size = 11
Range("A7").Select
ActiveSheet.PivotTables("DrawData").PivotSelect "Grant[All]", xlLabelOnly + _
xlFirstRow, True
Selection.Font.Size = 11
ActiveSheet.PivotTables("DrawData").PivotSelect "", xlDataAndLabel, True
Selection.Copy

Range("A35").Select.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End With

Getting an Object required error on the isolated red highlighted text portion above. What am I missing? (Object obviously, but need a bit of a nudge on this). Thanks.


jski

 

[Micron]

Maybe more like

Range("A35").Select
Selection.PasteSpecial....

or

With Range("A35")
  .Select
  .PasteSpecial...

as PasteSpecial is a method of a Range object and you haven't provided a range to paste into. Maybe see

Range.PasteSpecial method (Excel)

Office VBA reference topic

docs.microsoft.com

In fact, I have the root of the Office object model as a bookmark

Office Visual Basic for Applications (VBA) reference

This reference is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

docs.microsoft.com

 

[jski21]

Thanks Micron. Still having a bit of trouble with this. Boiled it down to the following:

'***Copy Pivot table
ActiveSheet.PivotTables("DrawData").PivotSelect "", xlDataAndLabel, True
Selection.Copy


'***PasteSpecial Values of Pivot table
Range("A35").PasteSpecial Operation:=xlPasteSpecialOperationPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


The copy statement works fine. The PasteSpecial statement now gives me a "PasteSpecial method of Range class failed" error.

I'm certain I'm missing something rather minor but can't determine what.


Thanks.

 

[Micron]

The copy statement works fine.

If that means it doesn't error, it's not the same thing as "it works". Perhaps you copied nothing? Put a break point on the paste line, open a new text document and ctrl+V in it. If you paste nothing, you didn't copy anything, which will raise that error AFAIK.
Don't you need to provide a name for the PivotSelect method?
I've also read that you can't initiate this from the macro dialog as your selection will be lost, so how are you running the code?

 

[RoryA]

It should be Paste:=xlPasteSpecialOperationPasteFormats not Operation:=xlPasteSpecialOperationPasteFormats

 

[jski21]

Thanks gentlemen. Micron--I put the break in and the copy statement did work. The code presented initially is extracted from a longer sub, used to modify an unfriendly excel downloaded from a reporting platform. Rory--tried that line of code and received the same error message. I fiddled a bit and got this to work:

'***Copy Pivot table
ActiveSheet.PivotTables("DrawData").PivotSelect "", xlDataAndLabel, True
Selection.Copy

'***PasteSpecial Values of Pivot table
Range("A35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Would not have been able to solve this without each of your comments and guidance. Thanks!

jski