Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Hi, while this has been brought up many times, I'm still having
difficulty finding a macro or some short cut that works well to merge two worksheets into one. I want to be able to merge them easily and in such a way that it could be automatically updated when new data is added to one or both of the original sheets. The sheets have the same format-same headings and number of columns-so I would like to get solely the data from the second sheet without the headings to add to the first sheet, which would retain the headings. I'm new to VBA, so I may be missing something obvious, but if I can be pointed in the right direction, I would greatly appreciate it. Thanks in advance. ~M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Sub MergeTwoWorksheets()
Dim sh1 As Worksheet, sh2 As Worksheet Dim sh3 As Worksheet, rng1 As Range Dim rng2 As Range Set sh1 = Worksheets(1) Set sh2 = Worksheets(2) Set sh3 = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) Set rng1 = sh1.Range("A1").CurrentRegion Set rng2 = sh2.Range("A1").CurrentRegion rng1.Copy sh3.Range("A1") rng2.Copy sh3.Range("A1").Offset( _ rng1.Rows.Count, 0) End Sub if you don't have blank rows in your data. If you meant worksheets in separate workbooks rather than two worksheets in the same workbook, then that would require some modifications. -- Regards, Tom Ogilvy wrote in message ps.com... Hi, while this has been brought up many times, I'm still having difficulty finding a macro or some short cut that works well to merge two worksheets into one. I want to be able to merge them easily and in such a way that it could be automatically updated when new data is added to one or both of the original sheets. The sheets have the same format-same headings and number of columns-so I would like to get solely the data from the second sheet without the headings to add to the first sheet, which would retain the headings. I'm new to VBA, so I may be missing something obvious, but if I can be pointed in the right direction, I would greatly appreciate it. Thanks in advance. ~M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Thanks a lot! It works very well and I don't have to keep deleting the
"master" sheet! Now that I have this, any ideas how to get rid of the header on the top of the second sheet (for example, where in the above code would I make the modifications for this to happen)? ~M |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Sub MergeTwoWorksheets()
Dim sh1 As Worksheet, sh2 As Worksheet Dim sh3 As Worksheet, rng1 As Range Dim rng2 As Range Set sh1 = Worksheets(1) Set sh2 = Worksheets(2) Set sh3 = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) Set rng1 = sh1.Range("A1").CurrentRegion Set rng2 = sh2.Range("A1").CurrentRegion Set rng2 = rng2.Offset(1,0).Resize(rng.rows.count-1) rng1.Copy sh3.Range("A1") rng2.Copy sh3.Range("A1").Offset( _ rng1.Rows.Count, 0) End Sub -- Regards, Tom Ogilvy wrote in message ups.com... Thanks a lot! It works very well and I don't have to keep deleting the "master" sheet! Now that I have this, any ideas how to get rid of the header on the top of the second sheet (for example, where in the above code would I make the modifications for this to happen)? ~M |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Thanks a lot! Do you also happen to know if and how I could alter the
code so that any pair of worksheets could be merged, not just the first two, by having user input or something? Thanks again, the code works perfectly! ~M |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging worksheets
Never mind, I figured it out. Thanks again for your help!
~M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging worksheets | Excel Discussion (Misc queries) | |||
Merging worksheets together | Excel Discussion (Misc queries) | |||
merging worksheets | Excel Discussion (Misc queries) | |||
Merging Worksheets | Excel Discussion (Misc queries) | |||
Merging of Worksheets | Excel Discussion (Misc queries) |