How to fix errors ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _False

[nguyenvanquy1984]

Regarding this excel file, I downloaded it from the source: AutoCAD LT 2013 - How to measure total length?!

Because I don't know how to upload this excel file here. Please download the file from the source above.

it gives a debug error. Please see attached photo. I use excel 360

Thank you for your consideration!

 

[nguyenvanquy1984]

Please see this photo

 

[nguyenvanquy1984]

Please download here.

Thank you for your consideration!

 

[zero269]

Hi Nguyen,

When it comes to sharing Sample Data, you'll want to use the Forums Excel Add-In here: XL2BB - Excel Range to BBCode
It makes it much easier and people are more willing to offer assistance if you make it easy to provide the data in an easy to replicate format using that Add-in.
Plus, most people - if not everyone here- will not download Macro-enabled workbooks they don't trust...

When sharing VBA code, just paste it in the post and use the quick wrap for VBA code icon; labeled VBA.

As for the image I saw; I'm no expert, but I think all you need to Paste Values Only is the following.
You can try this macro as a standalone test on any empty cell. Be sure to copy something first...

Sub Paste_Values_Only()

    'Paste Values
    ActiveSheet.PasteSpecial Format:="Text"

End Sub

Hope this helps...

 

[Alex Blakenburg]

Do you know what that macro is actually meant to do ?
Paste normally follows a "Copy" and there is no copy.

In case anyone else wants to buy into this thread here is the code in the picture:

Sub Paste_and_calculate()

' Paul Gander - June 2017

    Dim lastrow As Long
   
    lastrow = Range("input").Rows.Count
    Range("a2:b" & lastrow).Value = ""
   
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
    Selection.Name = "Input"
    lastrow = Range("input").Rows.Count
    Range("b1:b" & lastrow).FillDown
   
End Sub

And here is the Sheet as an XL2BB
Note: There is a button on the sheet that calls that macro.

Total length v02.xls
	A	B	C	D	E	F
1				AutoCAD units:	mm
2
3				Output units:	m
4				Precision:	3	(No. of decimal places)
5
6
7
8
9
10				Total length =	0.000m
11
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(NOT(ISERROR(SEARCH("length",A1))),IF(MID(TRIM(A1),8,1)="=",VALUE(MID(TRIM(A1),9,SEARCH(",",TRIM(A1))-9)),VALUE(MID(TRIM(A1),8,28))),IF(NOT(ISERROR(SEARCH("circumference",A1))),VALUE(MID(TRIM(A1),15,35)),IF(NOT(ISERROR(SEARCH("perimeter",A1))),VALUE(MID(TRIM(A1),11,31)),"")))
E10	E10	=IF(Output_unit<>"ft-in",FIXED(Output,Output_precision)&OFFSET(Unit_list_base,0,MATCH(Output_unit,Output_unit_list,0)-1),INT(Output)&"'-"&FIXED(MOD(Output,1)*12,Output_precision)&"""")

Named Ranges
Name	Refers To	Cells
Input	=Sheet1!$A$1:$A$45	B1
Output_precision	=Sheet1!$E$4	E10
Output_unit	=Sheet1!$E$3	E10

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=Input_unit_list
E3	List	=Output_unit_list