In all of my reports, I have to do a trend chart about the last 12 months performance, and for each new month, there is a new column or row. Since I am using formula, I need to copy them and paste them to the new row or column so I needed a way to find the last row/column.

When I use the macro ?

For doing a trend chart in order to find the last column/row and copy the formula to the new column/row.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

For the column:

Sub test()
Dim lCol As Long
Dim lRow As Long
With ActiveSheet
' change 2 by the first row number to copy
lCol = Cells(2, Columns.Count).End(xlToLeft).Column
' change 5 by the last row number to copy
lRow = Cells(5, lCol).Row
Range(Cells(2, lCol), Cells(lRow, lCol)).Copy Cells(2, lCol + 1)
' remove those 2 lines to not paste as values at the old column
Range(Cells(2, lCol), Cells(lRow, lCol)).Copy
Cells(2, lCol).PasteSpecial (xlPasteValues)
End With
End Sub

For the row:

Sub test()
Dim lCol As Long
Dim lRow As Long
With ActiveSheet
' change 2 by the first column number to copy
lCol = Cells(Rows.Count, 2).Column
' change 5 by the last column number to copy
lRow = Cells(Rows.Count, 5).End(xlUp).Row
Range(Cells(lRow, 5), Cells(lRow, lCol)).Copy Cells(lRow + 1, 2)
' remove those 2 lines to not paste as values at the old row
Range(Cells(lRow, 5), Cells(lRow, lCol)).Copy
Cells(lRow, 2).PasteSpecial (xlPasteValues)
End With
End Sub