VBA Macro for copying data to another sheet

[davida_vsn]

Hi,

Using macro, I want to copy "customer order" data from one sheet to another but my original data contains blanks: separating each customer's order. Therefore after the space, is the next customer's name and order date.
The original data has 9 columns and varying no of rows depending on the no of items ordered by each customer.

I would like to copy the each customer's order including the name and order date into another sheet to form a continuous list.

Any macro code to help with this?

I am having trouble attaching a copy of the data.

 

[hiker95]

davida_vsn,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of each of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

 

[davida_vsn]

davida_vsn,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of each of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

Thanks for your reply. I am using PC, windows 7 and Excel 2007.

Below is the order data (raw data):

Date:	Order Date	Date From: 24/02/2004 Date To: 28/02/2004
Orders:	Completed;
						Page 1 of 2
Customer:	Dan				Date:	27/02/2004
Name:	Dan				Due:	27/02/2004
Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
ABHJ001	AdoreJ001	16.00		16.00	162.5000	0.00	2,600.00	2,600.0000
ABHJ002	AdoreJ002	16.00	PiecesU	16.00	170.6250	0.00	2,730.00	2,730.0000
ABHJ006	AdoreJ006	21.00		21.00	107.1429	0.00	2,250.00	2,250.0000
PLP001	Plump001	60.00		60.00	13.4921	0.00	809.52	809.5200
PLP003	Plump003	4.00		4.00	219.0476	0.00	876.19	876.1900
PLP004	Plump004	7.00		7.00	428.5714	0.00	3,000.00	3,000.0000
HIP010	Hipe010	1.00		1.00	304.7619	0.00	304.76	304.7600
WER001	Weaver001	9.00		9.00	317.4600	0.00	2,857.14	2,857.1400
WER002	Weaver002	28.00		28.00	333.3333	0.00	9,333.33	9,333.3300
NYC002	Nicer002	1.00		1.00	103.1743	0.00	103.17	103.1700
		461.00	461.00		67,702.17	67,702.1700
Customer:	BELL				Date:	28/02/2004
Name:	BELL				Due:	28/02/2004
Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
ABHJ001	AdoreJ001	5.00	CaseU	5.00	2,730.0000	0.00	13,650.00	13,650.0000
ABHJ002	AdoreJ002	5.00	CaseU	5.00	2,730.0000	0.00	13,650.00	13,650.0000
HIP009	Hipe009	2.00	CaseU	2.00	5,485.7143	0.00	10,971.43	10,971.4300
		118.00	118.00		416,881.90	416,881.9000
Customer:	BOL				Date:	26/02/2004
Name:	BOL				Due:	26/02/2004
Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
ABHJ001	AdoreJ001	15.00	CaseU	15.00	2,730.0000	0.00	40,950.00	40,950.0000
ABHJ002	AdoreJ002	10.00	CaseU	10.00	2,730.0000	0.00	27,300.00	27,300.0000

<tbody>
</tbody>

Below is the sheet (in the same workbook) where I want the compiled data:

Date	Customer	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot

<tbody>
</tbody>

Hope this makes sense.
Thank you.

 

[hiker95]

davida_vsn,

the sheet (in the same workbook) where I want the compiled data:

Can we have a screenshot of what this worksheet looks like with the data from the order data worksheet?



Is the following screenshot accurate as to cell locations in the order data worksheet?

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Date:	Order Date	Date From: 24/02/2004 Date To: 28/02/2004
2	Orders:	Completed;
3							Page 1 of 2
4	Customer:	Dan				Date:	27/02/2004
5	Name:	Dan				Due:	27/02/2004
6	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
7	ABHJ001	AdoreJ001	16		16	162.5	0	2,600.00	2,600.00
8	ABHJ002	AdoreJ002	16	PiecesU	16	170.625	0	2,730.00	2,730.00
9	ABHJ006	AdoreJ006	21		21	107.1429	0	2,250.00	2,250.00
10	PLP001	Plump001	60		60	13.4921	0	809.52	809.52
11	PLP003	Plump003	4		4	219.0476	0	876.19	876.19
12	PLP004	Plump004	7		7	428.5714	0	3,000.00	3,000.00
13	HIP010	Hipe010	1		1	304.7619	0	304.76	304.76
14	WER001	Weaver001	9		9	317.46	0	2,857.14	2,857.14
15	WER002	Weaver002	28		28	333.3333	0	9,333.33	9,333.33
16	NYC002	Nicer002	1		1	103.1743	0	103.17	103.17
17			461	461		67,702.17	67,702.17
18
19
20	Customer:	BELL				Date:	28/02/2004
21	Name:	BELL				Due:	28/02/2004
22	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
23	ABHJ001	AdoreJ001	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
24	ABHJ002	AdoreJ002	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
25	HIP009	Hipe009	2	CaseU	2	5,485.71	0	10,971.43	10,971.43
26			118	118		416,881.90	416,881.90
27	Customer:	BOL				Date:	26/02/2004
28	Name:	BOL				Due:	26/02/2004
29	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
30	ABHJ001	AdoreJ001	15	CaseU	15	2,730.00	0	40,950.00	40,950.00
31	ABHJ002	AdoreJ002	10	CaseU	10	2,730.00	0	27,300.00	27,300.00
32
Sheet1

 

[davida_vsn]

davida_vsn,



Can we have a screenshot of what this worksheet looks like with the data from the order data worksheet?



Is the following screenshot accurate as to cell locations in the order data worksheet?

Thanks Hiker95,

Your screenshot of the order worksheet is accurate.

See below the screenshot of the compiled data worksheet (with data from the order data worksheet):

Date	Customer	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
27/02/2004	Dan	ABHJ001	AdoreJ001	16.00		16.00	162.5000	0.00	2,600.00	2,600.0000
27/02/2004	Dan	ABHJ002	AdoreJ002	16.00	PiecesU	16.00	170.6250	0.00	2,730.00	2,730.0000
27/02/2004	Dan	ABHJ006	AdoreJ006	21.00		21.00	107.1429	0.00	2,250.00	2,250.0000
27/02/2004	Dan	PLP001	Plump001	60.00		60.00	13.4921	0.00	809.52	809.5200
27/02/2004	Dan	PLP003	Plump003	4.00		4.00	219.0476	0.00	876.19	876.1900
27/02/2004	Dan	PLP004	Plump004	7.00		7.00	428.5714	0.00	3,000.00	3,000.0000
27/02/2004	Dan	HIP010	Hipe010	1.00		1.00	304.7619	0.00	304.76	304.7600
27/02/2004	Dan	WER001	Weaver001	9.00		9.00	317.4600	0.00	2,857.14	2,857.1400
27/02/2004	Dan	WER002	Weaver002	28.00		28.00	333.3333	0.00	9,333.33	9,333.3300
27/02/2004	Dan	NYC002	Nicer002	1.00		1.00	103.1743	0.00	103.17	103.1700
28/02/2004	BELL	ABHJ001	AdoreJ001	5.00	CaseU	5.00	2,730.0000	0.00	13,650.00	13,650.0000
28/02/2004	BELL	ABHJ002	AdoreJ002	5.00	CaseU	5.00	2,730.0000	0.00	13,650.00	13,650.0000
28/02/2004	BELL	HIP009	Hipe009	2.00	CaseU	2.00	5,485.7143	0.00	10,971.43	10,971.4300
26/02/2004	BOL	ABHJ001	AdoreJ001	15.00	CaseU	15.00	2,730.0000	0.00	40,950.00	40,950.0000
26/02/2004	BOL	ABHJ002	AdoreJ002	10.00	CaseU	10.00	2,730.0000	0.00	27,300.00	27,300.0000

<tbody>
</tbody>

Note: the order data worksheet usually contains more customers i.e. more than 3, I've only shown a sample.

Hope this helps. Thanks.

 

[hiker95]

davida_vsn,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Sample worksheets:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Date:	Order Date	Date From: 24/02/2004 Date To: 28/02/2004
2	Orders:	Completed;
3							Page 1 of 2
4	Customer:	Dan				Date:	27/02/2004
5	Name:	Dan				Due:	27/02/2004
6	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
7	ABHJ001	AdoreJ001	16		16	162.5	0	2,600.00	2,600.00
8	ABHJ002	AdoreJ002	16	PiecesU	16	170.625	0	2,730.00	2,730.00
9	ABHJ006	AdoreJ006	21		21	107.1429	0	2,250.00	2,250.00
10	PLP001	Plump001	60		60	13.4921	0	809.52	809.52
11	PLP003	Plump003	4		4	219.0476	0	876.19	876.19
12	PLP004	Plump004	7		7	428.5714	0	3,000.00	3,000.00
13	HIP010	Hipe010	1		1	304.7619	0	304.76	304.76
14	WER001	Weaver001	9		9	317.46	0	2,857.14	2,857.14
15	WER002	Weaver002	28		28	333.3333	0	9,333.33	9,333.33
16	NYC002	Nicer002	1		1	103.1743	0	103.17	103.17
17			461	461		67,702.17	67,702.17
18
19
20	Customer:	BELL				Date:	28/02/2004
21	Name:	BELL				Due:	28/02/2004
22	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
23	ABHJ001	AdoreJ001	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
24	ABHJ002	AdoreJ002	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
25	HIP009	Hipe009	2	CaseU	2	5,485.71	0	10,971.43	10,971.43
26			118	118		416,881.90	416,881.90
27	Customer:	BOL				Date:	26/02/2004
28	Name:	BOL				Due:	26/02/2004
29	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
30	ABHJ001	AdoreJ001	15	CaseU	15	2,730.00	0	40,950.00	40,950.00
31	ABHJ002	AdoreJ002	10	CaseU	10	2,730.00	0	27,300.00	27,300.00
32
Order

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Customer	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Compiled Data

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Customer	Item/Account	Description	Order Qty	Unit	Confirmed	Unit Price Excl	Disc %	Confirm Tot	Order Tot
2	27/02/2004	Dan	ABHJ001	AdoreJ001	16		16	162.5	0	2,600.00	2,600.00
3	27/02/2004	Dan	ABHJ002	AdoreJ002	16	PiecesU	16	170.625	0	2,730.00	2,730.00
4	27/02/2004	Dan	ABHJ006	AdoreJ006	21		21	107.1429	0	2,250.00	2,250.00
5	27/02/2004	Dan	PLP001	Plump001	60		60	13.4921	0	809.52	809.52
6	27/02/2004	Dan	PLP003	Plump003	4		4	219.0476	0	876.19	876.19
7	27/02/2004	Dan	PLP004	Plump004	7		7	428.5714	0	3,000.00	3,000.00
8	27/02/2004	Dan	HIP010	Hipe010	1		1	304.7619	0	304.76	304.76
9	27/02/2004	Dan	WER001	Weaver001	9		9	317.46	0	2,857.14	2,857.14
10	27/02/2004	Dan	WER002	Weaver002	28		28	333.3333	0	9,333.33	9,333.33
11	27/02/2004	Dan	NYC002	Nicer002	1		1	103.1743	0	103.17	103.17
12	28/02/2004	BELL	ABHJ001	AdoreJ001	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
13	28/02/2004	BELL	ABHJ002	AdoreJ002	5	CaseU	5	2,730.00	0	13,650.00	13,650.00
14	28/02/2004	BELL	HIP009	Hipe009	2	CaseU	2	5,485.71	0	10,971.43	10,971.43
15	26/02/2004	BOL	ABHJ001	AdoreJ001	15	CaseU	15	2,730.00	0	40,950.00	40,950.00
16	26/02/2004	BOL	ABHJ002	AdoreJ002	10	CaseU	10	2,730.00	0	27,300.00	27,300.00
17
Compiled 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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CompileData()
' hiker95, 03/21/2014, ME765816
Dim wo As Worksheet, wc As Worksheet
Dim Area As Range, sr As Long, er As Long, n As Long, nr As Long
Application.ScreenUpdating = False
Set wo = Sheets("Order")
Set wc = Sheets("Compiled Data")
For Each Area In wo.Range("B3", wo.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    n = er - (sr + 2)
    nr = wc.Cells(wc.Rows.Count, "A").End(xlUp).Row + 1
    wo.Range("G" & sr).Copy wc.Range("A" & nr & ":A" & nr + n - 1)
    wo.Range("B" & sr).Copy wc.Range("B" & nr & ":B" & nr + n - 1)
    wo.Range("A" & sr + 3 & ":I" & er).Copy wc.Range("C" & nr)
  End With
Next Area
With wc
  .Columns("A:K").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CompileData macro.

 

[hiker95]

davida_vsn,

In the above macro, you can change the next two lines of code:

Set wo = Sheets("[B]Order[/B]")
Set wc = Sheets("[B]Compiled Data[/B]")

By replacing Order with the actual Order worksheet name.

And, by replacing Compiled Data with the actual Compiled Data worksheet name.

 

[davida_vsn]

thanks a million hiker95. This works perfectly fine.

 

[hiker95]

davida_vsn,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.