Modify the last active row

[Janet H]

This macro (see below) works well for what I want to do however, it only
works on the first row. I need to make it work on the last active row -
whatever row that may be.

Here's what I'm trying to do - the user completes the row of the worksheet
and if they want the calculated amount to be discounted (20%), they click the
appropriate button. The originally calculated amount is replaced by the
discounted amount and a new line is created with the appropriate codes and
the discount. (The two lines together equal the original amount.)

I don't know how to tell it to use the last active row.

BTW - I'm really pushing the envelope of my little bit of programming
knowledge as I"m a newbie.

Thanks much.

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
ActiveSheet.Protect

(M, N & O22 are holding cells for calcs. They don't change and can be
overridden each time the macro is run)

 

[Gary Keramidas]

this will get you the last row in column A
Option Explicit
Dim LastRow As Long

Sub Macro2()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LastRow
End Sub

then just use the lastrow variable in your procedure

 

[Mike Fogleman]

Janet, try this.

Sub Discount()
Dim LastRow As Long
Dim NewRow As Long
Dim vCost

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
NewRow = LastRow + 1
vCost = Range("J" & LastRow).Value

ActiveSheet.Unprotect
Range("A" & LastRow).copy Range("A" & NewRow)
Range("C" & LastRow & ":G" & LastRow).copy Range("C" & NewRow)
Range("H" & NewRow & ":I" & NewRow).Value = 0
Range("J" & NewRow).Value = vCost * 0.2
Range("j" & LastRow).Value = vCost * 0.8
Range("k" & LastRow).Value = "Reduced" & Chr(10) & "for Atty"
Range("k" & NewRow).Value = "Atty Fees"
Range("a" & NewRow + 1).Activate
ActiveSheet.Protect
End Sub

Just curious why you didn't want column B copied?

Mike F

 

[Janet H]

Mike, this is exactly what I needed. I have been trying to figure this out
for days. I am such a newbie, working on big company project and can't find
anyone to help me except you support folks.

Your answer is so helpful. (I'm not copying B because there is a macro
button there that does something else, if selected).

I've copied your macro just as you posted it except my first few lines are
set up differently (see them below). Is this what is causing it to be in
break mode? Sub Discount is highlighted yellow, with arrow.
______
Sub Discount()

'
' Discount Macro
' Macro recorded 9/5/2005 by Janet Herring
'

Dim lastrow As Long
Dim newrow As Long
Dim vcost
_________

 

[Janet H]

Actually, It looks like it's related to these two lines -

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A" + lastrow").Copy Range("A" & newrow)

 

[Mike Fogleman]

Range("A" + lastrow").Copy Range("A" & newrow)
is not the same as what I gave you. Make sure this line looks exactly like
this:
Range("A" & LastRow).copy Range("A" & NewRow)

You had + instead of &, and a half quote after lastrow.

Mike F