Copy Two Columns into one column

[adamsm]

Hi,

The following code copies data from columns I and K starting from row 25 to column A of the active sheet.

[vba]Sub CopyColumns()
Dim LR1 As Long, LR2 As Long, i As Integer
For i = 9 To 11
LR1 = Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(25, i), Cells(LR2, i)).Copy Destination:=Cells(LR1 + 1, 1)
Next i
Application.CutCopyMode = False
End Sub[/vba]
How could the code be changed so that it would copy the column contents of I and K starting from row 25 to row 33 to column D of the sheet "Data" starting from row5.

Any help on this would be kindly appreciated.

Thanks in advance.

[Simon Lloyd]

Try this:[VBA]Sub CopyColumns()
Range("I25:I33").copy Destination:= Sheets("Data").Range("D5")
Range("K25:K33").copy Destination:= Sheets("Data").Range("D" & rows.count).End(xlUp).offset(1,0)
End Sub[/VBA]

[adamsm]

Thanks for the help Simon. The code works fine when the user copies data for the first time. Meaning it does copies data from column I and K to column D at initial.

But when the user runs the macro for the second time it copies the column I contents to the row 5 and column K instead of continuing from the last empty row of the "data" sheet.

How could the code be changed so that it copies data to the last empty row of the "Data" sheet?

Any help on this would be kindly appreciated.

Thanks in advance.

[shrivallabha]

Untested. Try Simon's edited code:
[VBA]

Sub CopyColumns()
Dim lRow As Long
lRow = Sheets("Data").Range("D" & Rows.Count).End(xlUp)(2).Row
If lRow < 5 Then lRow = 5
Range("I25:I33").Copy Destination:=Sheets("Data").Range("D" & lRow)
Range("K25:K33").Copy Destination:=Sheets("Data").Range("D" & Rows.Count).End(xlUp)(2)
End Sub

[/VBA]

[adamsm]

Thanks for the help Shrivallabha