Macro error when copying pasting from clipboard

[danhendo888]

Trying to copy a column and paste using the clipboard on the Home ribbon.
When I do this it usually converts the text values to numbers (for numbers that I copied and pasted externally)

Run-time error: 1004
Paste method of worksheet class failed

Sheets("Sheet2").Select
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Paste
Application.CommandBars("Office Clipboard").Visible = False
ActiveCell.Offset(-1, 0).Range("Table1[[#Headers],[GL Account]]").Select

 

[DataBlake]

replace

ActiveSheet.Paste

with

selection.paste

or

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[DataBlake]

also

Application.CutCopyMode = False

means you are exiting cut/copy mode so you should put it at the end of copy pasting

 

[danhendo888]

Hmm, it's not working for me. Getting a 438 error.

When I extract data from a software and paste it into my sheet, there are numbers that are stored as text and that I need to convert to numbers.
I usually do this by selecting the range, opening the clipboard, copying to clipboard, hitting Paste All. This will convert the numbers stored as text, to numbers.

So that's what I recorded in my macro. Not sure if there is a better way to do it.
File link FYI

https://1drv.ms/x/s!AvjBsEPEq12ngS9ITAe84IHxcI2Q?e=EMRSJg

 

[MARK858]

Try...

With Sheets("Sheet2").ListObjects("Table1").DataBodyRange
  .Value = .Value
End With

 

[danhendo888]

Hi Mark,
https://1drv.ms/x/s!AvjBsEPEq12ngS9ITAe84IHxcI2Q?e=tWGu3b

If I want to create the macro button in Sheet1 and convert to numbers the column K in Sheet2.
What would the code be?

I tried to use ListColumns(11) but am getting an error.

 

[MARK858]

Sub ABC()
 With Sheets("Sheet2").ListObjects("Table1").ListColumns(11).Range
   .Value = .Value
 End With
End Sub

 

[danhendo888]

OMG I love you
Thank you Mark!