Sour Deaux
New Member
- Joined
- Sep 19, 2019
- Messages
- 1
I am working with a user form (Cost Sheet) that allows for input into disjointed fields that was created for aesthetic purposes, but does not lend itself to easy data analysis. I'm attempting to automate the process of collecting all of the data line combinations and put them in columnar table format. When I am analyzing these forms, there are several of them that have to be processed. I have been able to put together VBA to identify a selected folder and copy ranges from each workbook, but not sure how to arrange it by combining each element of the input sections. I need someone to help me get started with VBA for this process. Thank you.
<tbody>
</tbody>
<tbody>
</tbody>
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | ||||||||||||||||||||
2 | Supplier Name | Supplier A | Alignment to Warehouses | |||||||||||||||||
3 | City, State | Chicago, IL | Los Angeles, CA | Chicago, IL | Los Angeles, CA | |||||||||||||||
4 | Account Number | 123456789 | Zip | 60007 | 90001 | 60007 | 90001 | |||||||||||||
5 | Collect Pricing & Capacity by Ship Point | Prepaid Pricing by Item by Warehouse | ||||||||||||||||||
6 | ||||||||||||||||||||
7 | City, State | 5-Digit Zip | Shipping Location 1 | Shipping Location 2 | 2010 | 2020 | 2030 | 2040 | ||||||||||||
8 | Item Information | Los Angeles, CA | 90001 | Chicago, IL | 60007 | New Orleans, LA | Phoenix, AZ | St. Louis, MO | Las Vegas, NV | |||||||||||
9 | Shipping Location 1 | Los Angeles, CA | 90001 | Collec Cost | Case | Collect Cost | Case | Delivered Price | Delivered Price | Delivered Price | Delivered Price | |||||||||
10 | Shipping Location 2 | Chicago, IL | 60007 | Product Number | Description | Weight | Cube | Price/Case | Capacity | Price/Case | Capacity | |||||||||
11 | Shipping Location 3 | 111 | Widget 1 | 5.7 | 0.3 | $1.00 | 1,000 | $1.50 | 1,000 | $1.60 | $1.20 | $1.55 | $1.10 | |||||||
12 | Shipping Location 4 | 222 | Widget 2 | 5.2 | 0.4 | $1.00 | 1,000 | $1.50 | 1,000 | $1.60 | $1.20 | $1.55 | $1.10 | |||||||
13 | Shipping Location 5 | |||||||||||||||||||
14 | Shipping Location 6 | |||||||||||||||||||
15 | Shipping Location 7 | |||||||||||||||||||
16 | Shipping Location 8 | |||||||||||||||||||
17 | Shipping Location 9 | |||||||||||||||||||
18 | Shipping Location 10 | |||||||||||||||||||
19 | Shipping Location 11 | |||||||||||||||||||
20 | Shipping Location 12 | |||||||||||||||||||
21 | Shipping Location 13 | |||||||||||||||||||
22 | Shipping Location 14 | |||||||||||||||||||
23 | Shipping Location 15 | |||||||||||||||||||
24 | ||||||||||||||||||||
25 | ||||||||||||||||||||
26 | ||||||||||||||||||||
27 | ||||||||||||||||||||
28 | ||||||||||||||||||||
29 | ||||||||||||||||||||
30 |
<tbody>
</tbody>
Cost Sheet
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Supplier | Account Number | Shipping City | Shipping State | Shipping Zip | Product Number | Product Description | Product Weight | Product Cube | Freight Term | Case Cost | Shipping Location Capacity | Warehouse | Transportation Cost (added by a separate process) | Warehouse Landed Cost |
2 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.00 | 1,000 | 2010 | $0.10 | $1.10 |
3 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.00 | 1,000 | 2020 | $0.07 | $1.07 |
4 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.00 | 1,000 | 2030 | $0.05 | $1.05 |
5 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.00 | 1,000 | 2040 | $0.08 | $1.08 |
6 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.50 | 1,000 | 2010 | $0.10 | $1.60 |
7 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.50 | 1,000 | 2020 | $0.07 | $1.57 |
8 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.50 | 1,000 | 2030 | $0.05 | $1.55 |
9 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Collect | $1.50 | 1,000 | 2040 | $0.08 | $1.58 |
10 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.00 | 1,000 | 2010 | $0.10 | $1.10 |
11 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.00 | 1,000 | 2020 | $0.07 | $1.07 |
12 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.00 | 1,000 | 2030 | $0.05 | $1.05 |
13 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.00 | 1,000 | 2040 | $0.08 | $1.08 |
14 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.50 | 1,000 | 2010 | $0.10 | $1.60 |
15 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.50 | 1,000 | 2020 | $0.07 | $1.57 |
16 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.50 | 1,000 | 2030 | $0.05 | $1.55 |
17 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Collect | $1.50 | 1,000 | 2040 | $0.08 | $1.58 |
18 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Prepaid | $1.20 | 1,000 | 2020 | $0.00 | $1.20 |
19 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 111 | Widget 1 | 5.7 | 0.3 | Prepaid | $1.10 | 1,000 | 2040 | $0.00 | $1.10 |
20 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Prepaid | $1.60 | 1,000 | 2010 | $0.00 | $1.60 |
21 | Supplier A | 123456789 | Chicago | IL | 60007 | 111 | Widget 1 | 5.7 | 0.3 | Prepaid | $1.55 | 1,000 | 2030 | $0.00 | $1.55 |
22 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Prepaid | $1.20 | 1,000 | 2020 | $0.00 | $1.20 |
23 | Supplier A | 123456789 | Los Angeles | CA | 90001 | 222 | Widget 2 | 5.2 | 0.4 | Prepaid | $1.10 | 1,000 | 2040 | $0.00 | $1.10 |
24 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Prepaid | $1.60 | 1,000 | 2010 | $0.00 | $1.60 |
25 | Supplier A | 123456789 | Chicago | IL | 60007 | 222 | Widget 2 | 5.2 | 0.4 | Prepaid | $1.55 | 1,000 | 2030 | $0.00 | $1.55 |
<tbody>
</tbody>
Desired Output