Macro question: how to copy the last row from a range with criteria by means of macro

[Paulus1983]

Hello! I would very much appreciate it if someone could help me with a specific macro question.
Question: what will a macro look like that allows me to copy the final row from a specific range (eg. B5:M27) to the next row The first row (row 4) cannot be copied, this is a permanent / descriptive row. So I'm looking for a macro that searches for the final row in a pre-set range and copies this row to the next, when the macro is activated.

- In the example above the final row can be ranging from 5 to 27.

I really would appreciate your help!

Thanks in advance

 

[Paulus1983]

This worked great but I would like to change the criteria. Instead of determining the row to copy based on the final row that contains data in column B, I would like to determine the row to copy based on the cell selected in column A.
eg. if the relevant range is B5:M27 and A8 is selected, the macro should copy B8:M8 and past it to the next.

Thank you very much for all help!


This additional question is also posted in a seperate thread:
http://www.mrexcel.com/forum/showthread.php?t=539674
----------------------------------------------------------------------

 

[jmthompson]

Not testes

Sub CopyRow()
Dim Last as Long
Last = ActiveCell.Row
Range(Cells(Last + 1, "B"), (Cells(Last + 1, "M"))).Value = Range(Cells(Last, "B"), (Cells(Last, "M"))).Value
End Sub

</pre>

 

[Paulus1983]

Hi Jen,

Thank you again for your help. The code works very well. I'm starting to get a little familiar with VBE and combined your code with another code I received:

Option Explicit

Sub CopyRow()

Dim r As Long
Dim NextRow As Long

If Not Intersect(Range("A5:A27"), ActiveCell) Is Nothing Then

r = ActiveCell.Row

NextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

Range(Cells(r, "B"), Cells(r, "M")).Copy Destination:=Cells(NextRow, "B")

Else

MsgBox "Active cell is not located within A5:A27...", vbExclamation

End If

End Sub


And came up with the following:

Option Explicit

Sub CopyRow()

Dim last As Long

If Not Intersect(Range("A5:A27"), ActiveCell) Is Nothing Then

Last = ActiveCell.Row

Range(Cells(Last + 1, "B"), (Cells(Last + 1, "M"))).Value = Range(Cells(Last, "B"), (Cells(Last, "M"))).Value

Else

MsgBox "Active cell is not located within A5:A27...", vbExclamation

End If

End Sub
This code follows the criteria that the active cell should be placed in column A but doesn't make a count in column B to find out what the final cell is.

Still don't know exactly what it says but I pretty much understand what it does.

Thanks very much!

 

[jmthompson]

Try this

Option Explicit

Sub CopyRow()

Dim Last As Long
Dim Next as long

If Not Intersect(Range("A5:A27"), ActiveCell) Is Nothing Then

Last = ActiveCell.Row
Next = Cells(Rows.Count, "B").End(xlUp).Row + 1

Range(Cells(Next, "B"), (Cells(Next, "M"))).Value = Range(Cells(Last, "B"), (Cells(Last, "M"))).Value

Else

MsgBox "Active cell is not located within A5:A27...", vbExclamation

End If

End Sub