Paste row under existing data

[Marin Vukovic]

Hello to the group, this is the first time I'm here and I'm getting a
little desperate looking for a solution. I hope I'll find someone who
can help me with this. Anyone who helps me has a virtual beer from me,
cause this is rather urgent and I don't know how to do it.

Okay, here's the deal. I'm using Excel 2000. I have a worksheet which is
used to enter different kinds of data (no kidding). When it's all
entered, it creates a row of 5 cells which needs to be copied to another
worksheet in another file for tracking purposes. Furthermore, it needs
to be done for numerous such rows and then they're copied one after
another in that other file. Now, I know I could do it manually and just
use copy/paste, but I have to try to make it do that automatically.

I was thinking I could use VBA and macros. I know how to record a macro
and make a button which will copy and paste that row in another
worksheet. What I don't know is how to force it to skip the rows which
have data in them and paste the next row UNDER the existing data. For
instance...

The row looks like this, very simple:

cell1 cell2 cell3 cell4 cell5

Now let's imagine that I already have the first row copied in that other
worksheet, like here:

A B C D E
1 cell1 cell2 cell3 cell4 cell5
2
3

When I press the button which triggers the macro, how do I make Excel to
skip row 1 and automatically paste the new data in row 2, under the
existing data? How do I automatically get this:

A B C D E
1 cell1 cell2 cell3 cell4 cell5
2 cell1 cell2 cell3 cell4 cell5

And so on and so on... the next one would paste in row 3, then row 4
etc. Is it possible to do that with macros or any function in Excel? How
do I make Excel look for the next empty row and paste data there?

Thanks in advance, I would appreciate any help, even if it's just "nope,
can't be done"! I hope I made it clear enough and it's easy to
understand what I need.

Marin

 

[Otto Moehrbach]

Marin
Let's say that you selected the row (in the source file) that you want
to be copied. You then did Edit - Copy or whatever you do to put it into
the clipboard. Then let's say that you want to paste it to Column A of the
destination file. In your recorded macro, where it states the destination
row, replace it with something like:
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
The copied row will be pasted into the row below the end of your data in
Column A of the destination file.
Post your code if you have any trouble with this. HTH Otto

 

[Ken Wright]

The routine sets references to each of two sheets, Data1 where the data will be
copied from and Data2 where it will be copied to.

The lrow = wks2.Cells(Rows.Count, "A").End(xlUp).Row bit starts at
cells(rows.count, "A") on Data2 which will be Cells(65536, "A") which is the
same as A65536 and then uses End(xlUp) to work upwards and find the first cell
with data in it. The .row bit returns the row number of that cell. The last
bit of the sub then simply copies rng1 which is your range to copy each time,
and then pastes it to range(A lrow) (ie A2, A3, A4, A5... etc). The lrow + 1
bit means that it paste to the cell that is one cell down from the current last
row. This gets recalculated every time you hit the button, so will increment by
one each time.

Sub CopyData()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim rng1 As Range
Dim lrow As Long

Set wks1 = Sheets("Data1")
Set wks2 = Sheets("Data2")
Set rng1 = wks1.Range("A1:E1")

lrow = wks2.Cells(Rows.Count, "A").End(xlUp).Row

With rng1
.Copy wks2.Cells(lrow + 1, "A")
End With

End Sub

 

[Marin Vukovic]

Otto and Ken,

Thank you very much, both of you! It works fine both ways and I guess
I'll have to decide which one to use. I appreciate your help.

Marin

 

[Ken Wright]

A pleasure - either will do you fine.