| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Code - Pasting Values
Here is my code - I'm getting an error at the **. I'm trying to paste the
result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in Code - Pasting Values
Give this a try...
ActiveSheet.Unprotect Range("A24").Copy Range("A25") Range("C24:G24").Copy Range("C25") Range("H25").Value = 0 Range("I25").Value = 0 Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy Range("J25").PasteSpecial Paste:=xlPasteValues ActiveSheet.Protect -- HTH... Jim Thomlinson "Janet H" wrote: Here is my code - I'm getting an error at the **. I'm trying to paste the result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in Code - Pasting Values
Wow. That's like speaking English. Thank you very much.
I've got more to go - that was just part of my macro. I am working through it a piece at a time. I may be back. . . "Jim Thomlinson" wrote: Give this a try... ActiveSheet.Unprotect Range("A24").Copy Range("A25") Range("C24:G24").Copy Range("C25") Range("H25").Value = 0 Range("I25").Value = 0 Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy Range("J25").PasteSpecial Paste:=xlPasteValues ActiveSheet.Protect -- HTH... Jim Thomlinson "Janet H" wrote: Here is my code - I'm getting an error at the **. I'm trying to paste the result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in Code - Pasting Values
Recording macros is a good start but a whole lot of stuff gets added that is
not really necessary. A hint is where you see a select followed by a selection, you can pretty much get rid of it... if you know how it works... If you need more help just ask. -- HTH... Jim Thomlinson "Janet H" wrote: Wow. That's like speaking English. Thank you very much. I've got more to go - that was just part of my macro. I am working through it a piece at a time. I may be back. . . "Jim Thomlinson" wrote: Give this a try... ActiveSheet.Unprotect Range("A24").Copy Range("A25") Range("C24:G24").Copy Range("C25") Range("H25").Value = 0 Range("I25").Value = 0 Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy Range("J25").PasteSpecial Paste:=xlPasteValues ActiveSheet.Protect -- HTH... Jim Thomlinson "Janet H" wrote: Here is my code - I'm getting an error at the **. I'm trying to paste the result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in Code - Pasting Values
Jim, now that I've completed my macro, I need it to run on any line. I know
that is something like Range("a" & "activecell.row") but I can't get that to work. (Values in M,N,O don't change) I've ** the values that can vary. The column will always be the same but the row will change. I always want to start on the last row that has a value in "a" See macro below Thanks again. I'm learning. ActiveSheet.Unprotect ** Range("A24").Copy Range("A25") **Range("C24:G24").Copy Range("C25") **Range("H25").Value = 0 **Range("I25").Value = 0 **Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy **Range("J25").PasteSpecial Paste:=xlPasteValues Range("o22").Formula = "=M22*0.8" Range("o22").Copy **Range("j24").PasteSpecial Paste:=xlPasteValues **Range("k24").Value = "Reduced" & Chr(10) & "for Atty" **Range("k25").Value = "Atty Fees" ** Range("a26").Select (This is just to return them to a logical place on the form) ActiveSheet.Protect End Sub "Jim Thomlinson" wrote: Recording macros is a good start but a whole lot of stuff gets added that is not really necessary. A hint is where you see a select followed by a selection, you can pretty much get rid of it... if you know how it works... If you need more help just ask. -- HTH... Jim Thomlinson "Janet H" wrote: Wow. That's like speaking English. Thank you very much. I've got more to go - that was just part of my macro. I am working through it a piece at a time. I may be back. . . "Jim Thomlinson" wrote: Give this a try... ActiveSheet.Unprotect Range("A24").Copy Range("A25") Range("C24:G24").Copy Range("C25") Range("H25").Value = 0 Range("I25").Value = 0 Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy Range("J25").PasteSpecial Paste:=xlPasteValues ActiveSheet.Protect -- HTH... Jim Thomlinson "Janet H" wrote: Here is my code - I'm getting an error at the **. I'm trying to paste the result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in Code - Pasting Values
I am a little lost as to what exactly you are trying to accomplish so I will
give you some general pointers that will hopefull help you out... To get the last populated Cell in Column a you want to use code similar to this dim lngLastRow as Long 'Variable to store the row number of the last cell lngLastRow = cells(rows.count, "A").End(xlUp).row 'Store the row number of the last cell in A range("A" & lngLastrow).copy range("B" & lngLastRow + 1) 'Copy A??? to B??? one row down -- HTH... Jim Thomlinson "Janet H" wrote: Jim, now that I've completed my macro, I need it to run on any line. I know that is something like Range("a" & "activecell.row") but I can't get that to work. (Values in M,N,O don't change) I've ** the values that can vary. The column will always be the same but the row will change. I always want to start on the last row that has a value in "a" See macro below Thanks again. I'm learning. ActiveSheet.Unprotect ** Range("A24").Copy Range("A25") **Range("C24:G24").Copy Range("C25") **Range("H25").Value = 0 **Range("I25").Value = 0 **Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy **Range("J25").PasteSpecial Paste:=xlPasteValues Range("o22").Formula = "=M22*0.8" Range("o22").Copy **Range("j24").PasteSpecial Paste:=xlPasteValues **Range("k24").Value = "Reduced" & Chr(10) & "for Atty" **Range("k25").Value = "Atty Fees" ** Range("a26").Select (This is just to return them to a logical place on the form) ActiveSheet.Protect End Sub "Jim Thomlinson" wrote: Recording macros is a good start but a whole lot of stuff gets added that is not really necessary. A hint is where you see a select followed by a selection, you can pretty much get rid of it... if you know how it works... If you need more help just ask. -- HTH... Jim Thomlinson "Janet H" wrote: Wow. That's like speaking English. Thank you very much. I've got more to go - that was just part of my macro. I am working through it a piece at a time. I may be back. . . "Jim Thomlinson" wrote: Give this a try... ActiveSheet.Unprotect Range("A24").Copy Range("A25") Range("C24:G24").Copy Range("C25") Range("H25").Value = 0 Range("I25").Value = 0 Range("J24").Copy Range("M22").PasteSpecial Paste:=xlPasteValues Range("N22").Formula = "=M22*0.2" Range("N22").Copy Range("J25").PasteSpecial Paste:=xlPasteValues ActiveSheet.Protect -- HTH... Jim Thomlinson "Janet H" wrote: Here is my code - I'm getting an error at the **. I'm trying to paste the result of my formula as a value. Also, I don't like the RC cell references. I tried to change them manually but then the macro doesn't run. I don't do much visual basic. I record a macro and then TRY to edit it. Thanks for any help. ActiveSheet.Unprotect Range("A24").Select Selection.Copy Range("A25").Select ActiveSheet.Paste Range("C24:G24").Select Application.CutCopyMode = False Selection.Copy Range("C25").Select ActiveSheet.Paste Range("h25").Select ActiveCell = "0" Range("i25").Select ActiveCell = "0" Range("J24").Select Application.CutCopyMode = False Selection.Copy Range("M22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("N22").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=rc[-1]*0.2" Range("N22").Select Application.CutCopyMode = False Selection.Copy Range("J25").Select **Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Protect End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why can't I use SUM when pasting HTML code into new sheet. | Excel Worksheet Functions | |||
| running code after pasting data | Excel Programming | |||
| Streamline copying and pasting code | Excel Programming | |||
| Over-Eager code is pasting 3 times | Excel Programming | |||
![Janet H[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
