how to copy data into next available row + macro?

[nathan663]

I have the following macro:

Sub Button40_Click()
'
' Button40_Click Macro
' Macro recorded 24/03/2009 by Nathan Gallichan
'
'
Range("C4:N4").Select
ActiveWindow.SmallScroll Down:=-3
Windows("Book2").Activate
Range("A2").Select
ActiveCell.FormulaR1C1 = "Individual Approved"
Range("B2").Select
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("C4:N4").Select
Selection.Copy
Windows("Book2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B14").Select
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
ActiveWindow.SmallScroll Down:=18
Range("L22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L23").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMinimized
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L24").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L26").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F9").Select
End Sub

Could some one please tell me what i need to change so that the date being copied accross will go into the next free row. At the moment the data i want to transfer will copy into row 2. I want this to be the next available row. However i do not know what to add or where to add this into the macro.

 

[Mike Blackman]

Hi,

Maybe something along these lines might do it for you but I'm getting lost in your code;

Dim lRow As Long, Wb As Worksheet, Wb2 As Worksheet

Set Wb = Workbooks("New Accounts - KYC Checklists - Jersey new world 2009").ActiveSheet
Set Wb2 = Workbooks("Book2").ActiveSheet

lRow = Wb2.Range("C" & Rows.count).End(xlUp).Row

Wb2.Range("C" & lRow) = Wb.Range("L22")

 

[nathan663]

this did not work... is there not a code i can put at the start of this macro that simply starts the macro at the next available row?

some sort of code you could use at the beggining of all macros?