Hello all.
Long time lurker, first time poster.
I need help extracting data from a software based Excel export - it's not pretty.
Essentially, we export a budget from our software but need it in a different format to upload into a different system.
I can do some of this code already, but would like a nice tidy macro from start to finish.
1. Create 2 additional worksheets; one called Section1 the other called Section2. Have 4 columns in these sheets named: Code1/Code2/Qty/Amount
2. In original sheet split column G (by the space delimiter) into Code1 and Code2 columns.
Here's the kicker:
3. Copy columns/range H/I/L/N UNTIL column B says "Subtotal - Section 1" so in this case, the range would be "H3:I9, L3:L9, N3:N9" (if it's easier, happy to do one big range H3:N9 and delete out the irrelevant columns once pasted). The number of rows in the budget will change depending on the job (eg. the next one I do the subtotal may be in row 50). Paste into Section1 sheet.
4. Copy Subtotal - Section 1 amount (this example $15,000) into Section1 sheet under the Amount Column (under the costs) and populate the Code1 (value: 1000), Code2 (value: 12000), Qty (value: 1) in that row.
5. In Section1 sheet delete out any rows that don't have a code
Extracting Section2 Budget:
Same as steps 3 - 5 above, except I only want the Costs/Revenue from the Section2 information.
I have been trying to do this for days, so any assistance would be greatly appreciated! I don't know how to link the file, but below is a screen shot.
TIA!
Long time lurker, first time poster.
I need help extracting data from a software based Excel export - it's not pretty.
Essentially, we export a budget from our software but need it in a different format to upload into a different system.
I can do some of this code already, but would like a nice tidy macro from start to finish.
1. Create 2 additional worksheets; one called Section1 the other called Section2. Have 4 columns in these sheets named: Code1/Code2/Qty/Amount
2. In original sheet split column G (by the space delimiter) into Code1 and Code2 columns.
Here's the kicker:
3. Copy columns/range H/I/L/N UNTIL column B says "Subtotal - Section 1" so in this case, the range would be "H3:I9, L3:L9, N3:N9" (if it's easier, happy to do one big range H3:N9 and delete out the irrelevant columns once pasted). The number of rows in the budget will change depending on the job (eg. the next one I do the subtotal may be in row 50). Paste into Section1 sheet.
4. Copy Subtotal - Section 1 amount (this example $15,000) into Section1 sheet under the Amount Column (under the costs) and populate the Code1 (value: 1000), Code2 (value: 12000), Qty (value: 1) in that row.
5. In Section1 sheet delete out any rows that don't have a code
Extracting Section2 Budget:
Same as steps 3 - 5 above, except I only want the Costs/Revenue from the Section2 information.
I have been trying to do this for days, so any assistance would be greatly appreciated! I don't know how to link the file, but below is a screen shot.
TIA!
