how to merge two databases
This works perfectly!
Much appreciated.
Thanks a lot,
Mr D.
-----Original Message-----
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.D elete
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"
--
Regards,
Tom Ogilvy
"Mr D." wrote in message
...
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.
-----Original Message-----
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
--
Regards,
Tom Ogilvy
"Mr. D" wrote in message
...
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
.
.
|