Lisa Harris
New Member
- Joined
- Sep 19, 2016
- Messages
- 17
Hi,
I have been frantically trying different methods and searching for help for a while to complete this, but unable to find a solution that allows you to combine sheets if column headers do not remain constant.
I need a macro that will produce a summary sheet combining an initial despatch log ('Before' tab) with a store orders / back orders log post despatch ('after' tab), so we can quickly view the total amount each one had of each item in the end. However, as time goes on, columns and rows get added in to a master (inserted as well as added to the end) so the 'after' sheet is likely to have additional columns (items) or Rows (stores) to the 'before' sheet and in a different order where new columns have been inserted as a result.
Is there VBA that will combine the two sheets, adding in the new 'unique' columns and rows in the correct order where necessary? then looking up the values and summing?
Any help would be much appreciated - thanks in advance
example of Before sheet:
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>
example of After sheet:
<colgroup><col span="4"><col><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>
I have been frantically trying different methods and searching for help for a while to complete this, but unable to find a solution that allows you to combine sheets if column headers do not remain constant.
I need a macro that will produce a summary sheet combining an initial despatch log ('Before' tab) with a store orders / back orders log post despatch ('after' tab), so we can quickly view the total amount each one had of each item in the end. However, as time goes on, columns and rows get added in to a master (inserted as well as added to the end) so the 'after' sheet is likely to have additional columns (items) or Rows (stores) to the 'before' sheet and in a different order where new columns have been inserted as a result.
Is there VBA that will combine the two sheets, adding in the new 'unique' columns and rows in the correct order where necessary? then looking up the values and summing?
Any help would be much appreciated - thanks in advance
example of Before sheet:
| A | B | C | D | E | F | G | H | I | J | |
| 1 | ||||||||||
| 2 | ||||||||||
| 3 | shop no | shop name | type | new format? | limes | apples | bananas | pears | grapes | oranges |
| 4 | 4 | store A | type 1 | Y | 1 | 0 | 1 | 2 | 1 | 0 |
| 5 | 65 | Store B | type 2 | N | 0 | 0 | 1 | 3 | 1 | 0 |
| 6 | 77 | Store C | type 1 | Y | 2 | 0 | 1 | 1 | 1 | 2 |
| 7 | 896 | Store D | type 1 | Y | 2 | 0 | 1 | 1 | 1 | 0 |
| 8 | 43 | Store E | type 1 | Y | 1 | 0 | 2 | 1 | 1 | 2 |
| 9 | 256 | Store F | type 2 | N | 0 | 0 | 2 | 2 | 2 | 1 |
| 10 | 56 | Store G | type 1 | N | 2 | 0 | 2 | 2 | 2 | 2 |
| 11 | 432 | Store H | type 1 | Y | 0 | 0 | 2 | 4 | 2 | 2 |
| 12 | 32 | Store I | type 1 | Y | 1 | 0 | 1 | 3 | 4 | 4 |
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>
example of After sheet:
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
| 1 | |||||||||||||
| 2 | |||||||||||||
| 3 | shop no | shop name | type | new format? | limes | lemons | apples | bananas | pears1 | pears2 | TOTAL pears | grapes | oranges |
| 4 | 4 | store A | type 1 | Y | 1 | 1 | 0 | 1 | 2 | 1 | 3 | 1 | 0 |
| 5 | 65 | Store B | type 2 | N | 0 | 0 | 0 | 1 | 3 | 2 | 5 | 1 | 0 |
| 6 | 77 | Store C | type 1 | Y | 2 | 2 | 0 | 1 | 1 | 1 | 2 | 1 | 2 |
| 7 | 896 | Store D | type 1 | Y | 2 | 1 | 0 | 1 | 1 | 1 | 2 | 1 | 0 |
| 8 | 43 | Store E | type 1 | Y | 1 | 1 | 0 | 2 | 1 | 1 | 2 | 1 | 2 |
| 9 | 256 | Store F | type 2 | N | 0 | 1 | 0 | 2 | 2 | 1 | 3 | 2 | 1 |
| 10 | 56 | Store G | type 1 | N | 2 | 2 | 0 | 2 | 2 | 1 | 3 | 2 | 2 |
| 11 | 432 | Store H | type 1 | Y | 0 | 0 | 0 | 2 | 4 | 1 | 5 | 2 | 2 |
| 12 | 32 | Store I | type 1 | Y | 1 | 1 | 0 | 1 | 3 | 1 | 4 | 4 | 4 |
<colgroup><col span="4"><col><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>
