PDA

View Full Version : Solved: Copy after the last line of data



joelle
11-11-2005, 02:57 PM
Dear Experts,

Its late Friday afternoon and my work is still up to my neck ... and I so
hope someone could help me with a vb module to tailor this roadblock:

I have 2 worksheets called "sheetA" and "sheetB" and I would like
to include some codes (that I dont know how to write) as part of
an existing macro of mine so that this macro copies lines 10-20 of
sheetA and paste them below the last line of sheetB that has data.

SheetB always has data but I dont know where the last with data will be because data depends on how long the price quote takes and the data is generated by another macro.

Many many thanks in advance,

Nee

malik641
11-11-2005, 03:54 PM
Night time in Jersey :mkay and it's FREEZING!

Here's a little something to get you going :thumb
Option Explicit
Sub CopyPaste()
Dim iLastRowB As Long
iLastRowB = Sheets("sheetB").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("sheetA").Range("10:20").Copy
Sheets("sheetB").Range("A" & iLastRowB).PasteSpecial xlAll
Application.CutCopyMode = False
End Sub


HTH

malik641
11-11-2005, 04:02 PM
Sorry, forgot you said that you didn't know when the last row of sheetB was...so I fixed it up a bit :thumb

Just make sure that any data in sheetB has something (in the corresponding column) in the first row.

Option Explicit
Sub CopyPaste()
Application.ScreenUpdating = False
Dim iLastRowB As Long
Dim iLastCol As Long
Dim ActiveRows As Long
Dim MostRows As Long
Dim iCol As Long
iLastCol = Sheets("sheetB").Cells(1, Columns.Count).End(xlToLeft).Column
MostRows = 0
For iCol = 1 To iLastCol
ActiveRows = Sheets("sheetB").Cells(Rows.Count, iCol).End(xlUp).Row
If ActiveRows > MostRows Then MostRows = ActiveRows
Next iCol
Sheets("sheetA").Range("10:20").Copy
Sheets("sheetB").Range("A" & MostRows).PasteSpecial xlAll
Application.CutCopyMode = False
Sheets("sheetB").Range("A" & MostRows).Select
Application.ScreenUpdating = True
End Sub
There, that should do it :yes

joelle
11-11-2005, 04:34 PM
Joe -- Thanks so much!!

Its late and cold there and you would not mind writing codes!
They work and I'm so appreciative.

Btw, it is gorgeous here now its 4pm !!

Have a great night there -- keep warm!!

Nee

malik641
11-11-2005, 06:06 PM
Joe -- Thanks so much!!

Its late and cold there and you would not mind writing codes!
They work and I'm so appreciative.

Btw, it is gorgeous here now its 4pm !!

Have a great night there -- keep warm!!

NeeYou're welcome :friends:

And I'll be keeping warm when I move to Florida! :thumb WHOOOO!!!! :joy: