Merging data from 2 worksheets

[Sparky]

How do I merge data from 2 worksheets into 1 worksheet named Merged Data. This will all be in the same workbook.

Sheet1 has data for 301 rows. This starts in cell B2 across to cell I2 and goes down to row 301.

Sheet2 also has data for 301 rows. This starts in cell B2 across to cell G2 and goes down to row 301.

The data in both sheets for column B has a unique number.

I require the data to appear in the "Merged Data" worksheet in numerical order.

Thanks in advance to anyone who may be able to help.

 

[hiker95]

Sparky,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Sparky]

Thanks for the reply Hiker95

Still using Excel 2003

The worksheets Rep 284 Rep 384 & Required both have different reports. I would like both to appear in a third sheet named Merged Data. As you can see the Merged Data sheet has them both sorted in numerical order. I realize I could copy and paste, sort etc but I would like it coded from which I could run it via a macro button.

First worksheet

Excel Workbook
	B	C	D	E	F	G	H	I
1	Number
2	5370	XJ32	5002	AC6MND	600	B	601	B
3	5371	XJ32	5002	AC6MND	600	A	601	A
4	5372	XJ32	5002	CC6JKL	700	A	701	A
5	5374	XJ32	5002	AC6PEB	600	A	601	A
6	5375	XJ32	5002	AC6MND	600	B	601	B
7	5376	XJ32	5002	AC6PEB	700	B	701	B
8	5378	XJ32	5002	CC6JKL	600	B	601	D
9	5379	XJ32	5002	AC6MND	600	B	601	B
Rep 284 Rep 384

Excel 2003

Second worksheet

Excel Workbook
	B	C	D	E	F	G
2	5373	10AG	HOT	3AC	2AC	LIGHT
3	5377	10AG	HOT	3AC	2AC	LIGHT
Required

Excel 2003

Desired result

Excel Workbook
	A	B	C	D	E	F	G	H
1	Number
2	5370	XJ32	5002	AC6MND	600	B	601	B
3	5371	XJ32	5002	AC6MND	600	A	601	A
4	5372	XJ32	5002	CC6JKL	700	A	701	A
5	5373	10AG	HOT	3AC	2AC	LIGHT
6	5374	XJ32	5002	AC6PEB	600	A	601	A
7	5375	XJ32	5002	AC6MND	600	B	601	B
8	5376	XJ32	5002	AC6PEB	700	B	701	B
9	5377	10AG	HOT	3AC	2AC	LIGHT
10	5378	XJ32	5002	CC6JKL	600	B	601	D
Merged Data

Excel 2003

Once again thanks for your interest

 

[Yahya]

try this

Sub Test()
Dim Fws As Worksheet, F2ws As Worksheet, Tws As Worksheet
Set Fws = Sheets("Rep 284 Rep 384")
Set F2ws = Sheets("Required")
Set Tws = Sheets("Merged Data")
    Fws.Range("A1:H" & Fws.Range("A" & Rows.Count).End(xlUp).Row).Copy Tws.Range("A1")
    F2ws.Range("b2:G" & F2ws.Range("B" & Rows.Count).End(xlUp).Row).Copy Tws.Range("A" & Tws.Range("A" & Rows.Count).End(xlUp).Row + 1)
    Tws.Range("A2:H" & Tws.Range("A" & Rows.Count).End(xlUp).Row).Sort Tws.Range("a2")
End Sub

 

[Sparky]

Yahya


Only the data from the "Required" worksheet is moving across to the "Merged Data" worksheet.

Thanks for the reply

 

[hiker95]

Sparky,


Sample worksheets before the macro:

Excel Workbook
	B	C	D	E	F	G	H	I
1	Number
2	5370	XJ32	5002	AC6MND	600	B	601	B
3	5371	XJ32	5002	AC6MND	600	A	601	A
4	5372	XJ32	5002	CC6JKL	700	A	701	A
5	5374	XJ32	5002	AC6PEB	600	A	601	A
6	5375	XJ32	5002	AC6MND	600	B	601	B
7	5376	XJ32	5002	AC6PEB	700	B	701	B
8	5378	XJ32	5002	CC6JKL	600	B	601	D
9	5379	XJ32	5002	AC6MND	600	B	601	B
10
Rep 284 Rep 384

Excel Workbook
	B	C	D	E	F	G
1
2	5373	10AG	HOT	3AC	2AC	LIGHT
3	5377	10AG	HOT	3AC	2AC	LIGHT
4
Required

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8
9
10
11
12
Merged Data

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Number
2	5370	XJ32	5002	AC6MND	600	B	601	B
3	5371	XJ32	5002	AC6MND	600	A	601	A
4	5372	XJ32	5002	CC6JKL	700	A	701	A
5	5373	10AG	HOT	3AC	2AC	LIGHT
6	5374	XJ32	5002	AC6PEB	600	A	601	A
7	5375	XJ32	5002	AC6MND	600	B	601	B
8	5376	XJ32	5002	AC6PEB	700	B	701	B
9	5377	10AG	HOT	3AC	2AC	LIGHT
10	5378	XJ32	5002	CC6JKL	600	B	601	D
11	5379	XJ32	5002	AC6MND	600	B	601	B
12
Merged Data

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MergeData()
' hiker95, 08/14/2011
' http://www.mrexcel.com/forum/showthread.php?t=571592
Dim wP As Worksheet, wQ As Worksheet, wM As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
Set wP = Worksheets("Rep 284 Rep 384")
Set wQ = Worksheets("Required")
If Not Evaluate("ISREF('Merged Data'!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Merged Data"
Set wM = Worksheets("Merged Data")
wM.UsedRange.Clear
LR = wP.Cells(Rows.Count, "B").End(xlUp).Row
wP.Range("B1:I" & LR).Copy wM.Range("A1")
NR = wM.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
LR = wQ.Cells(Rows.Count, "B").End(xlUp).Row
wQ.Range("B2:G" & LR).Copy wM.Range("A" & NR)
LR = wM.Cells(Rows.Count, "A").End(xlUp).Row
wM.Range("A2:H" & LR).Sort Key1:=wM.Range("A2"), Order1:=1, Header:=xlNo
wM.UsedRange.HorizontalAlignment = xlCenter
wM.Activate
Application.ScreenUpdating = True
End Sub

Then run the MergeData macro.

 

[Sparky]

Hiker95

Thanks for helping me out.

It works fine.

 

[hiker95]

Sparky,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.