CODEWITHSUNDEEP
excelvba
Luke P
Luke P

Reputation: 31

Copying Table Range and Pasting as Value

I have some code that updates an inventory sheet from another downloaded report by dropping formulas into some table cells, but I need to change them to just values instead of the formulas after updating.

I just need to copy the full column (which needs to be a variable amount of cells) and then paste it right back into the same spot, but as values. (Similar to manually copying, pasting, and then doing ctrl+v again to paste as values)

I'm open to any solutions as long as the cells become values.

I've tried standard ListObjects Range Copy code with PasteSpecial, but I get an error 438, "Object doesn't support this property or method" when it gets to the pasting line. I can't figure out what other options I have based on research I've done.

 '''This is the formula going into the cells

 ActiveCell.Formula = "=IF(ISERROR(GETPIVOTDATA(""Sec QTY Sum"", '[8-14-19 AM INVENTORY.xls]Sheet2'!$A$4,""Alias"",""" & y & """)),0,GETPIVOTDATA(""Sec QTY Sum"", '[8-14-19 AM INVENTORY.xls]Sheet2'!$A$4,""Alias"",""" & y & """))"

 '''The second line of this code is where it messes up with the error 438

 ActiveSheet.ListObjects("Table62").ListColumns(5).Range.Copy

 ActiveSheet.ListObjects("Table62").ListColumns(5).PasteSpecial Paste:=xlPasteValues

Trying to get values so that the spreadsheet isn't dependent on another spreadsheet being open/unchanged. Getting error message 438

Upvotes: 1

Views: 2126

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You're making a late-bound member call, so IntelliSense can't help you. Error 438 happens when you code blindfolded against Object or Variant, and try to guess what members exist on the runtime interface of the object you're working with... and the member happens to not exist.

ActiveSheet is an Object. Make it a Worksheet.

Dim sh As Worksheet
Set sh = ActiveSheet 'cast to Worksheet, don't work with Object.

Copying and pasting are Range operations, so work with the Range property:

With sh.ListObjects("Table62").ListColumns(5).Range
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With

None of the involved properties/objects here are Object, so everything is early-bound and you get autocompletion/intellisense every step of the way. Making sure your code is always early-bound is the single best way to avoid error 438.

That said, working with ActiveSheet is a trap here: if the active sheet doesn't have a table named Table62, expect run-time error 9 "subscript out of range". If you mean to be working with a specific worksheet, work with a specific sheet, not just whatever sheet happens to be active.

Upvotes: 2

Related Questions