Hi guys,
I'm a fair newbie when it comes to VBA automation and need help with the following process. I believe automating this could literally save me 12 hours or so of data entry and analysis per revision of this data. There's sometimes 30 revisions per project. Just need some help though.
Macro 1 - Named "Revision Breakup":
1. Add in new column next to each column containing data within the worksheet (or designate which columns need entering within base code)
2. Add new title to row 16 in each new column (this example) that relates to the old column next to it i.e ( =CONCATENATE(C12, " ", "Code") is what I would normally enter. Then apply this to each new column. This row designation needs to be adjustable as the formats change between architects.
3. In this example I need to combine the door number and designater to populate the door code column. (i.e = CONCATENATE(G17, H16)) This needs to populate the new column for the entire worksheet, as there are often blanks cells throughout so the simple CTRL+ALT+DOWN doesn't usually capture all.
4. Once the door code has been created, I need this added into each cell within the row to create essentially single specific series numbers for this cell i.e (=CONTATENATE($door code$, " ", target cell), this needs to be applied across the whole row. Then the door code on the next row needs to be applied across all other rows within the next row and so forth down the whole sheet.
5. Copy and paste each of these new columns ending in the word "code" into a new worksheet, titled after the title.
6. Save each of these new worksheets as an individual workbook titled as their title + Revision # (Can be individually entered in each VBA run).
Macro 2 - "Combine Revisions":
- Ideally, I would open "Title X Code - Rev A" and "Title X Code - Rev B" workbooks that would of been created from the "Revision Breakup" Macro over time.
- Also, a macro to combine all of the Rev A codes next to their Rev B codes but in a single worksheet.
1. Combine open worksheets into single worksheet named "Title X Code - Rev A vs Title X Code Rev B"
2. Place Rev A Data in column A, Place Rev B in column B as an example.
3. Conditional Format Column A & Column B to highlight Unique Values
4. Filter each column for unique value formatting.
5. Copy Unique Values from Column A into new sheet Titled "Changes", in Column A, Paste Column B values in first sheet into "Changes Sheet" sheet in Column G.
6. Text to columns all of row A, delimited with comma's and spaces
7. Conditional format Column A & Column G to show duplicate values
8. Sort Column A & Column G to match with duplicate value (Including all data aligned on the row between A-G & G-*)
9. Create New Row 1 and create Counts for Duplicate Value, Unique Value, all Data between B-F & H-*
Macro 3 - "Highlighted Changes"
In the combined sheet of all code columns that was created in Macro 2.
1. Highlight Rev A&B columns of 1 code type. Conditional Format. Highlight Unique Values.
2. Do this for the remaining code types, making sure only Rev A and B of that code type is the data range.
3. Filter the entire worksheet range for the conditional format colour.
This would be the base for this, with a few little tweaks here and there for potentially a pivot table macro.
Any help would be greatly appreciated. I've put an example of the source data sheet, and then an example of what the combined sheet from macro 2 would end up looking like.
Edit: Having trouble with the forum attachments.
Bookmarks