+ Reply to Thread
Results 1 to 5 of 5

Multiple VBA process Macro Help

  1. #1
    Registered User
    Join Date
    02-24-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    4

    Multiple VBA process Macro Help

    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.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Multiple VBA process Macro Help

    This is a help forum, not a "build it for you from scratch" forum.

    You could start by recording macros while you carry out your activities manually. They won't be perfect and they'll need tweaking to make them more efficient and generic ... but they'll give you a start, food for thought, and start you on the learning curve.

    Or, you could consider the Commercial Services forum. Or you could pay a developer to work with you.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-24-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    4

    Re: Multiple VBA process Macro Help

    Thanks for the reply.

    My intention wasn't for anyone to build it for me, more so if there was any glaring problems in being able to build something to the above specifications.

    As I said, I'm prepared to go down the learning curve to develop it, more so reaching out to see if what I'm even proposing is achievable in the first place.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Multiple VBA process Macro Help

    If you can do it manually, you can do it in code. The trick is making it re-usable.

  5. #5
    Registered User
    Join Date
    02-24-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    4

    Re: Multiple VBA process Macro Help

    My original thoughts is because the source data varies alot in it's format, that the code could simply be in a text file, with highlighted area's needing manual input?

    Like range of columns, starting cells etc.

    Or is this a bit archaic?

    The whole purpose is essentially comparing changes in 1 set of data to another.

    The problems arise from the fact that a lot of extra columns and rows are added/changed throughout the duration of the project and simple comparison software usually just highlights entire sections of data.

    The rows generally apply to each other. But row positions change all the time.

    I'm sorry if this doesn't make sense, my data is around extremely large scale construction management and the resultant specifications entailed within the buildings, I see enormous value in some of the ways tech and finance manage their data compared to our industry, where this type of data management isn't common practice at all.
    Last edited by Pykie; 02-24-2019 at 10:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to automate the copy/"cut" process of multiple selections using a VB macro
    By wittonline in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-01-2016, 09:55 PM
  2. [SOLVED] Modify macro to process multiple data
    By sans in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-22-2012, 04:36 AM
  3. process :Run macro on multiple sheets at once, not one after another
    By aoaeyy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-11-2008, 11:52 AM
  4. How to process multiple open files with a macro
    By garnet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2008, 07:03 PM
  5. how to process multiple files with a macro
    By mixel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-10-2007, 12:32 AM
  6. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2005, 06:06 AM
  7. [SOLVED] How to count process running time ( process not finished)
    By miao jie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2005, 02:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1