Macros

[Matt]

Hi I have written a Macro to take data from one worksheet
and paste it into another to keep a record as it were.
Unofrtunately though as it is pasting as a formula
everytime I change the data in the original sheet it
overwrites all the data in the new sheet. I would be
really grateful for any help anyone can give me on this as
it has stumped me. Listed below is the macro
Sub save()
'
' save Macro
' Macro recorded 23/04/2004 by matthewa
'

'
Sheets("Sheet2").Select
Dim MyText As String
Range("A2").Select
MyText = "=EUROLifestyle!R[18]C[1]"
ActiveCell.FormulaR1C1 = MyText
Range("B2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[20]C"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[17]C[1]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[19]C[-1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(EUROLifestyle!R[20]C[-2]=""Yes"",""Accidental
Damage"",""Standard"")"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[22]C[-3]"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(EUROLifestyle!R[23]C[-4]=""Yes"",""Accidental
Damgage"",""Standard"")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[25]C[-5]"
ActiveWindow.SmallScroll ToRight:=5
Range("J2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[28]C[-6]"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[32]C[-7]"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[35]C[-8]"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[37]C[-9]"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[37]C[-10]"
ActiveWindow.SmallScroll ToRight:=3
Range("O2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[41]C[-11]"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[44]C[-12]"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=EUROLifestyle!R[33]C[-8]"
Rows("2:2").Select
Range("J2").Activate
Selection.Insert Shift:=xlDown
Range("J2").Select
Sheets("EUROLifestyle").Select
End Sub

I know its a bit backward!

Thanks loads in advance.

Matt

 

[Patrick Molloy]

Your problem is not very clear.
suppose the data in your source worksheet is range
named "mydata", then yo want to copy the values to a NEW
worksheet...
Sub CopyData
DIM rSource as Range
DIM rTarget as Range
DIM WS_New as Worksheet

set rSource = Range("mydata")
' or
' Set rSource = Range("A5:G75") 'as an example

SET WS_New = Worksheets.Add

SET rTarget = WS_New.Range("A1")
with rTarget.Resize
(rSource.Rows.Count,rSource.Columns.Count)
..Value = rSource.Value
End With
End SUb

HTH
Patrick Molloy
Microsoft Excel MVP

 

[jim]

If all you want to do is copy the data from one sheet to
another why not just copy the whole sheet and paste select
values only into the other sheet. I.e

Sheets("Sheet1").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Where sheet1 is your original sheet

 

[SidBord]

Have you tried pasting just the values:
Selection.Paste Paste:=xlValues