Moving Cell Values Instead of Formulas to Another Sheet

  • Thread starter Thread starter shaqattack1992-google
  • Start date Start date
S

shaqattack1992-google

I would like to have a macro to so that with a push of a button, I can
move the values in cells A2:C2 to another spreadsheet (always that
range). When the values are changed, I would like to be able to hit
the same button and copy the new information to the next blank line on
the same sheet I copied to before.

I fould the following thread: http://tinyurl.com/2j9hg7

Sub copyfunction()

Dim fromrng As Range
Dim torng As Range

With Worksheets("Sheet1")
Set fromrng = .Range("a2:c2")
End With

With Worksheets("Sheet2")
Set torng = .Cells(.Cells.SpecialCells(xlLastCell).Row + 1,
"a")
End With

fromrng.Copy Destination:=torng

End Sub

This works great, but is there a way to change this so that when there
is a formula in cells A2:C2, the value will copy over instead of the
formula?

Also, as you can tell, I am a beginner at this kind of thing, but I
find myself having to rely on code more and more as I want to do more
complicated things. I'd appreciate any reference books or websites
that would help me learn. I don't like to just cut and paste code I
don't fully understand. Any help would be appreciated.

Thank You,

-Chad
 
fromrng.copy
torng.pastespecial paste:=xlpastevalues

or just assign the values

torng.resize(fromrng.rows.count,fromrng.columns.count).value _
= fromrng.value
 
fromrng.Copy
torng.PasteSpecial xlPasteValues
Application.CutCopyMode = False

--
Jim
|I would like to have a macro to so that with a push of a button, I can
| move the values in cells A2:C2 to another spreadsheet (always that
| range). When the values are changed, I would like to be able to hit
| the same button and copy the new information to the next blank line on
| the same sheet I copied to before.
|
| I fould the following thread: http://tinyurl.com/2j9hg7
|
| Sub copyfunction()
|
| Dim fromrng As Range
| Dim torng As Range
|
| With Worksheets("Sheet1")
| Set fromrng = .Range("a2:c2")
| End With
|
| With Worksheets("Sheet2")
| Set torng = .Cells(.Cells.SpecialCells(xlLastCell).Row + 1,
| "a")
| End With
|
| fromrng.Copy Destination:=torng
|
| End Sub
|
| This works great, but is there a way to change this so that when there
| is a formula in cells A2:C2, the value will copy over instead of the
| formula?
|
| Also, as you can tell, I am a beginner at this kind of thing, but I
| find myself having to rely on code more and more as I want to do more
| complicated things. I'd appreciate any reference books or websites
| that would help me learn. I don't like to just cut and paste code I
| don't fully understand. Any help would be appreciated.
|
| Thank You,
|
| -Chad
|
 
Back
Top