Error in Code - Pasting Values

J

Janet H

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
 
J

Jim Thomlinson

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
 
J

Janet H

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jim Thomlinson

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Janet H

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 said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Thomlinson

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 said:
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 said:
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 said:
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. . .

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help merging two VBA codes 2
Having difficulty copying and pasting on VBA 3
lenghty code 4
Pasting 2
Copy Paste Macro not Pasting Values when Blank 3
works in 2007 but not in 2010 0
Totals An Easier Way?? 5
wrong code 1

Top