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 | | | | | | | | | |
---|
|
---|
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 | | | | | | | | | | | |
---|
|
---|
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 | | | | | | | | | | | |
---|
|
---|
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.
Code:
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.