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

 

[jmthompson]

in your example, if the last row was 27, you just want it copied, as is, to 28?

 

[Paulus1983]

Sure, the final row is not relevant and can be unlimited.

Thank you for your quick reply!

 

[jmthompson]

Do you have a column where the data is contiguous? Meaning no blank cells until after your final row?

 

[Paulus1983]

Every row corresponds to one particular event. For every event/row there are 2 groups of columns. The first group of columns (B5:M27) contains general information that should be replicated in many instances, therefore the macro would be desirable. The 2nd group of columns would contain more specific information and would be filled out manually for every specific event. Both groups are split by one empty column, but of course an event is read horizontally, so written on both groups of columns (one row for every event).

I hope this helps.

 

[jmthompson]

2 questions:
Is it safe to assume that you will never have blanks in your B column?
When the row is copied, do you only want columns b-m included?

 

[Paulus1983]

The second group of columns will contain up to 12 different columns. most of the cells in the columns will be filled out (possbly not all). It could be that the macro will be activated several times without the second group of columns filled out or only partially.

The macro should only copy the final row from the first group of columns, leaving the second group untouched - so only b-m (starting from row 5).

 

[jmthompson]

Lots of ways to do this, but try

Sub CopyRow()
Last = Cells(Rows.Count, "B").End(xlUp).Row
    With Cells(Last, "B")
        .Offset(1, 0).Value = Cells(Last, "B").Value
        .Offset(1, 1).Value = .Offset(, 1).Value
        .Offset(1, 2).Value = .Offset(, 2).Value
        .Offset(1, 3).Value = .Offset(, 3).Value
        .Offset(1, 4).Value = .Offset(, 4).Value
        .Offset(1, 5).Value = .Offset(, 5).Value
        .Offset(1, 6).Value = .Offset(, 6).Value
        .Offset(1, 7).Value = .Offset(, 7).Value
        .Offset(1, 8).Value = .Offset(, 8).Value
        .Offset(1, 9).Value = .Offset(, 9).Value
        .Offset(1, 10).Value = .Offset(, 10).Value
        .Offset(1, 11).Value = .Offset(, 11).Value
        .Offset(1, 12).Value = .Offset(, 12).Value
    End With
End Sub

 

[jmthompson]

or this

Sub CopyRow()
Last = Cells(Rows.Count, "B").End(xlUp).Row
Range(Cells(Last + 1, "B"), (Cells(Last + 1, "M"))).Value = Range(Cells(Last, "B"), (Cells(Last, "M"))).Value
End Sub

 

[Paulus1983]

This is GREAT! Thank you very much, both work perfectly. I really appreciate your help!

Paul