PDA

View Full Version : [SOLVED] How to paste a cell as special values from another sheet?



roxnoxsox
02-05-2015, 05:40 AM
I have a spreadsheet where if a button is pressed, it will show cell A1 of sheet2 in cell G1 of Sheet1.
The code I'm using to do this is:

Sub CopyData()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet1").Range("G1")
End Sub


However, at the moment this is just copying the formula from A1 of sheet2. How can I modify this so that is just shows the value not the formula?

mancubus
02-05-2015, 05:55 AM
turn macro recorder on, do it manually, turn macro recorder off.

when you open VBE, yo wil see stg similar to:


Sub Macro1()
Sheets("Sheet2").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


after removing unnecessary "select"s/"selection"s:


Sub Macro1()
Sheets("Sheet2").Range("A1").Copy
Sheets("Sheet1").Range("G1").PasteSpecial Paste:=xlPasteValues
End Sub

roxnoxsox
02-06-2015, 04:38 AM
Perfect! Thanks :D