how to merge two databases

[Mr. D]

Hi,
I want to merge two databases which have the same exact
columns. I do this every other day and the databases vary
in the number of rows. By the way, they're located in the
same source worksheet.

Basically, I want to copy one of the databases and add it
to the other database (without copying the header).

Also since I have formulas and pivot tables using the
combined data, I want to name the range as "Data3".

I've tried searching for something similar in the past 6
months here but couldn't easily find anything relevant.

Thanks in advance,
Mr D

 

[Tom Ogilvy]

You want to put one set of data underneath the other?

assume the first starts in A1 and the second in M1 (at least column L is
blank, Headers in Row 1)

Sub copyData()
Dim rng as Range, rng1 as Range
set rng = Range("M1").CurrentRegion
set rng1 = rng.offset(1,0).Resize(rng.rows.count-1)
rng1.copy destination:=cells(rows.count,1).End(xlup)(2)
rng.Clearcontents
Range("A1").currentRegion.Name = "Data3"
End Sub

 

[Mr D.]

Hi Tom,

Thanks for the rapid response.
I tried this but somehow I got an error.
Maybe perhaps I didn't define the problem well enough.

1) yes, I do want to combine the databases with the second
one underneath the first one.
2) the first database is from a sheet called "Data-Current"
3) the second database is from a sheet called "Data-Future"
4) the combined database should be in a a sheet
called "Combined" (and the database is a named range
called "Data3")
5) question, when I run the macro again, will it also
clearout the old data? For example, if I run the macro
today and the combined database has 500 rows. Then if I
run the report 2 days from now, it only has 350 rows, will
the range still show the 500 rows or just the 300 rows?

Thanks again in advance and I look forward to hearing from
you.

Best regards,
Mr D.

 

[Tom Ogilvy]

I guess you fooled me when you said:

By the way, they're located in the same source worksheet.

Generally worksheet means a single sheet in an excel workbook.

Assume all worksheets exist

Sub copyData()
Dim rng as Range, rng1 as Range
set rng = Worksheets("Data_Current").Range("a1").CurrentRegion
set rng1 = Worksheets("Data_Future").Range("a1").CurrentRegion
set rng1 = rng1.offset(1,0).Resize(rng1.rows.count-1)
Worksheets("Combined").UsedRange.Rows.EntireRow.Delete
rng.copy Destination:=Worksheets("Combined").Range("A1")
rng1.copy Destination:=Worksheets("Combined) _
.Cells(rows.count,1).End(xlup)(2)
Worksheets("Combined").Range("A1") _
.currentRegion.Name = "Data3"
End Sub


Data3 will refer to the data currently in worksheet "combined"

 

[Mr D.]

This works perfectly!
Much appreciated.

Thanks a lot,
Mr D.