Please help me with this error of #ref

[Subvongola]

Hi guys

Please can you help me with this code. I have 2 workbook namely sub.xlsx and overall.xlsx. after I click on the button named update, col e72 to e110 should be copied to overall.xlsx but i do not know why the data pasted in overall.xlxs are all #ref. Why?

Cell e72 to e110 are in a workbook named sub.xlsx. e72 to e110 obtained the data from a1 to a38 (after some function).

Sub update()
Activesheet.range ("e72:e110").copy
Workbooks.open filename:="c:\xxx\overall.xlsx"
eCol = Activesheet.range. cells (1,columns.count).end (xltoleft).column
If eCol >= 1 then eCol = eCol + 1
Activesheet.range.cells (1,eCol).select
Activesheet.paste
Activesheet.range. save
Activesheet.range. close
End Sub

 

[wideboydixon]

Since the values are calculated from cells in another workbook, you can't really use the .Paste method. Try using .PasteSpecial instead:

Activesheet.PasteSpecial xlPasteValues

WBD

 

[Subvongola]

Hi,
I tried the pastespecial xlpastevalues but it state pastespecial method of worksheet class failed. How can I resolved this problem?