Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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 -- Gary "Janet H" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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 _________ "Mike Fogleman" wrote: 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" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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" wrote: 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" wrote in message ... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify the last active row
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 "Janet H" wrote in message ... 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" wrote: 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
Modify a UDF please? | Excel Worksheet Functions | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
how can I return the active row and active column? | Excel Programming | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) |