adding more records to existing data

D

davegb

I have an application where every month, I get a new set of data to add
to the old. The new data is on a separate sheet. I want to record a
macro that till copy the new data, add it at the bottom of the
complete, existing list of data, then sort on one field and put in a
total and subtotals. My only problem is getting the new data to be
added to the long list of all data. I know I can do this by stitching 2
recorded macros together with some code to tell XL where to put the new
data (at the bottom of the old data), but I was wondering if there's
any way to put the new data at the bottom of the existing data without
any writing any VBA? IOW, is there some sort of built-in feature in XL
that says, take list 1 at location x and add it to list 2 at location
y?

Any ideas? Or should I just stop being lazy and write a few lines of
code? Thanks in advance.
 
N

Nikos Yannacopoulos

Or should I just stop being lazy and write a few lines of code?
Click to expand...

Yes! That's exactly what you should do.

By the way, one thing which is key to your task, which you will never
get Excel to record in a macro, is ηος to "instruct" it to go down to
the end of existing data, and τηεν one more cell down (effectively, the
first empty one down) - that's because Excel records absolute cell
references rather than actions that got you there. So, assuming your
current active cell is A1, and you want to go the first empty cell in
Column A, you need this line of code:

ActiveCell.End(xlDown).Offset(1, 0).Select

Yet, this will get you into trouble if your active cell is the last
occupied one down, because it will try to go to the last row in the
sheet and then one more down, so it will err. To avoid this, you need
something like:

If IsEmpty(ActiveCell.Offset(1, 0)) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Offset(1, 0).Select
End If

You should be able to record most of the rest.

HTH,
Nikos
 
D

davegb

Nikos said:
Yes! That's exactly what you should do.

By the way, one thing which is key to your task, which you will never
get Excel to record in a macro, is ηος to "instruct" it togo down to
the end of existing data, and τηεν one more cell down(effectively, the
first empty one down) - that's because Excel records absolute cell
references rather than actions that got you there. So, assuming your
current active cell is A1, and you want to go the first empty cell in
Column A, you need this line of code:

ActiveCell.End(xlDown).Offset(1, 0).Select

Yet, this will get you into trouble if your active cell is the last
occupied one down, because it will try to go to the last row in the
sheet and then one more down, so it will err. To avoid this, you need
something like:

If IsEmpty(ActiveCell.Offset(1, 0)) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Offset(1, 0).Select
End If

You should be able to record most of the rest.

HTH,
Nikos
Click to expand...

Thanks for your reply, Nikos. I already knew that how to write the
code. Actually, XL can record relative or absolute addresses, but it
neither will get the data to paste immediately below the existing data.
Was just wondering if there was a simpler way.
 
Top