Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

Thanks again Ron. Very useful. In the end I used:

Sub EndCell()
Dim Lr As Long
Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("C2").Select
Selection.Copy
Cells(Lr, "C").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

This copied the formula in C2 from the last row containing data up to C2 as
hoped.

Thanks again.

"Ron de Bruin" wrote:

Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

Another way without selecting

Sub test()
Dim LastRow As Long
With Sheets("SAP (2)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("C2").AutoFill Destination:=.Range("C2:C" & LastRow) _
, Type:=xlFillDefault
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks again Ron. Very useful. In the end I used:

Sub EndCell()
Dim Lr As Long
Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("C2").Select
Selection.Copy
Cells(Lr, "C").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

This copied the formula in C2 from the last row containing data up to C2 as
hoped.

Thanks again.

"Ron de Bruin" wrote:

Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.

That's even better. Exactly what I was after. Many thanks.

"Ron de Bruin" wrote:

Another way without selecting

Sub test()
Dim LastRow As Long
With Sheets("SAP (2)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("C2").AutoFill Destination:=.Range("C2:C" & LastRow) _
, Type:=xlFillDefault
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks again Ron. Very useful. In the end I used:

Sub EndCell()
Dim Lr As Long
Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("C2").Select
Selection.Copy
Cells(Lr, "C").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

This copied the formula in C2 from the last row containing data up to C2 as
hoped.

Thanks again.

"Ron de Bruin" wrote:

Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.