Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to merge two databases
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to merge two databases
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to merge two databases
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to merge two databases
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").CurrentRegi on set rng1 = Worksheets("Data_Future").Range("a1").CurrentRegio n set rng1 = rng1.offset(1,0).Resize(rng1.rows.count-1) Worksheets("Combined").UsedRange.Rows.EntireRow.De lete 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick way merge 2 databases using a unique field. | Excel Discussion (Misc queries) | |||
databases | Excel Worksheet Functions | |||
Databases | Excel Discussion (Misc queries) | |||
DATABASES | Excel Discussion (Misc queries) | |||
how to merge two databases | Excel Programming |