| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macros
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 -----Original Message----- 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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macros
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 -----Original Message----- 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macros
Have you tried pasting just the values:
Selection.Paste Paste:=xlValues -----Original Message----- 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
| Making excel macros run Word macros | Excel Programming | |||
| Macro Size Limit / open macros with macros? | Excel Programming | |||
| Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
| Suppress the Disable Macros / Enable Macros Dialog | Excel Programming | |||
Hybrid Mode
