VBA to take result of SUM formula and paste into worksheet

[Stu Dapples]

Think I am losing the plot...

Simple - select a cell, copy - selct another cell in a different worksheet, paste.... Easy....

Origin cell has formaula in it (=SUM(A1:A5)

It copies OK but when I paste, I just get #Ref error - it is pasting the formula and not the value (so the cell now contains (=Sum(#Ref))

Tried PasteSpecial, Paste, xlPasteValues - nothing.... Am I missing something?!

Code:

Sheet3.Range("L28").Copy Sheet9.Range("K12").PasteSpecial = xlPasteValues

 

[Fluff]

Try it like

Sheet9.Range("K12").Value=Sheet3.Range("L28").Value

 

[gallen]

The paste special needs to be on the nest line, not as an argument to the copy

Sheet3.Range("L28").Copy
Sheet9.Range("K12").PasteSpecial = xlPasteValues

 

[gallen]

But Fluff's suggestion makes more sense!

 

[Stu Dapples]

Gallen - I need it to paste to this range in one scenario and elsewhere in others which I am coding through - I want to get the value on teh clipboard and then define where it is by confirming other states are true using IF commands - I will try the nest line!

Fluff - Thanks for the suggestion

 

[Stu Dapples]

The paste special needs to be on the nest line, not as an argument to the copy

Sheet3.Range("L28").Copy
Sheet9.Range("K12").PasteSpecial = xlPasteValues

Run Time Error '424':
Object required

???

 

[gallen]

Run Time Error '424':
Object required

???

Only thing that could cause that is if sheet3 or sheet9 doesn't exist. Check the spelling and sheet names. Remember that "Sheet9" refers to the object named Sheet9 and not a sheet named Sheet9.

Also confirm that the sheet isn't protected

 

[Stu Dapples]

 

[Stu Dapples]

View attachment 905

I retyped it based on the sheet names in the project explorer top left = Sheet9(Overview Quote)
Any thoughts?!

 

[Fluff]

Which line of code is highlighted when you click debug?