MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merge Sheets Together


Posted by Roy on September 17, 2001 10:30 AM

I have a app running 2 workbooks which both generate data on sheet 2. Is there a way i can merge both of the sheet 2's onto a different sheet in one of the open workbooks. I would like to do this with a macro if poss. The data on the sheets will change each time i want to merge them.

Desperate for this as it is for work

Thanks
Roy


Posted by Florian on September 17, 2001 12:13 PM

I have a app running 2 workbooks which both generate data on sheet 2. Is there a way i can merge both of the sheet 2's onto a different sheet in one of the open workbooks. I would like to do this with a macro if poss. The data on the sheets will change each time i want to merge them.

You need to describe exactly what you want to do (what do you mean by merge?)

Posted by Roy on September 17, 2001 1:31 PM

I want to just put the data from both sheets onto one sheet automatically when i press a button


Posted by Florian on September 17, 2001 1:40 PM

Added together? Side by side? One under the other?
Do you have headings/totals/formulas on the sheets?

Added together? Side by side? One under the other?
Do you have headings?


Posted by Roy on September 17, 2001 1:50 PM


Headings as follows


Date Code Product Qty Size

I want the destination sheet to show the other 2 sheets as 1, 1 under the other as if the data from the 2 sheets was just generated on the 1

Do u understand or do u req more info

Posted by Florian on September 17, 2001 4:17 PM


Try this :-

Sub Copy_Paste()
Dim source1 As Worksheet
Dim source2 As Worksheet
Dim dest As Worksheet
Dim rng As Range, rws As Long

Set source1 = Worksheets("Sheet1")
Set source2 = Worksheets("Sheet2")
Set dest = Worksheets("Sheet3")

dest.Cells.ClearContents
source1.Cells.Copy
ActiveSheet.Paste dest.Range("A1")
source2.Range(source2.Cells(2, 1), source2.Cells(65536, 5).End(xlUp)).Copy
ActiveSheet.Paste dest.Cells(65536, 1).End(xlUp)
End Sub

Posted by Florian on September 17, 2001 4:19 PM

Correction ......


Last line should read :-
ActiveSheet.Paste dest.Cells(65536, 1).End(xlUp).Offset(1,0)