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

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

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

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

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

merging worksheets
 

Never mind, I figured it out. Thanks again for your help!
~M