Posts by Tybaltcap

    Re: Inefficient VBA code- takes literally days to run


    Thanks a lot for that advice. Just as a bit of a background for why this tool creates so many worksheets/workbooks: We need to send out a list of employees to each manager in the company and have them provide input into who is on their team, and what roles they need. Essentially a bottoms up HR report.


    The parts for breaking out the individual workbooks and re-combining them work well (though maybe not in the template version I posted because it has waaaay more managers than we actually have ~200ish). The part that isn't working well is just copying the personal information from the merge sheet to the data table.

    Re: Inefficient VBA code- takes literally days to run


    Hi Stephen -


    Yes I tried it - but it errored out on the lastCol = LastC(merge) line. I don't know a whole lot about VBA, but I would assume that is probably because LastCol is already a Public sub (see my 2nd to last post)...not sure what the way is around that.


    If I can't improve my code, and go with the formula suggestion, I'd still like to automate that via some code - I can go hunting for the answer, but if anyone here wouldn't mind pointing me in the right way for how to use VBA to put a series of 'vlookup' formulas in, I'd greatly appreciate it.

    Re: Inefficient VBA code- takes literally days to run


    My current work around - because filling in this info is taking SO LONG - is just to do vlookups in each of the columns which generates my list almost instantaneous. Do you guys think that might be a better approach? That is to just completely replace the 'Fill Info' maco with just one just pastes (or runs) a vlookup in each of the columns on the Data Table sheet?


    If so - I'd love some guidance on the best way (I mean most efficient in terms of running it) to do that? Goal would be on the Data Table to have a vlookup for the Unique Key back to the RDBMergesheet.


    Thanks!

    Re: Inefficient VBA code- takes literally days to run


    Hey Roy


    Thanks for your continued help - sorry for the delay in getting back to you. If you put the file that I posted in a folder and go to the instructions tab - the first two macros (in buttons on that tab) will create all of the worksheets. The first one explodes out a matrix into lots of individual workbooks - one for each manager or 'Champion.' If you want to run it I'd suggest deleting the bottom 2/3rds of the list. The second one then pulls them back into one big list (RBGMergeSheet). I didn't bother to go into too much explanation of this earlier because those macros seem to work really well. The one that seems take so long is the 'Fill Info' macro which goes back to the merge sheet and pulls in the personal information.


    Unique ID is in Column A. Unique Key is in column M.


    Thanks others for your help - I'll go give them a try! LastR() and LastC() are just calling the LastRow() and LastCol()


    Re: Inefficient VBA code- takes literally days to run


    Hi Roy -


    Makes sense. Please find it attached. The code that is giving me the issues is in Module 4 - "Fill Info" - though I'm sure they all are pretty bad. Classic example of someone self teaching from recording and piecing together bits of code he's found on the internet. I needed to cleanse this of actual info, but there would be about 500-1000 'Champions' (managers) not a 1:1 match with End Users.


    Thank you so much for looking at this!


    Cheers,
    Tim


    forum.ozgrid.com/index.php?attachment/70623/

    Hi Gurus,


    I'm hoping you can help me improve a bit of code that I cobbled together as a VBA novice. The basic premise is to convert a matrix to a list. My matrix (Table 1) includes unique ID, personal info, unique key (same as unique ID) and assigned roles where an "X" matches the name (row) with the role (column). It is easy to create the reverse pivot table (Table 2) and the result is a table with Unique ID, Role, and 'X'. The code below goes back to Table 1 and stiches back personal info by matching unique ID (Table 1) with Unique Key (Table 2). It works - just VERRRRY slow as there will be 10-20k lines on Table 2.


    Table 1 (the matrix). In my code it is called 'RDBMergeSheet'


    Unique ID - Personal info - Unique Key (same as unique ID) - Roles (indicated with an X in the cell which aligns with the appropriate role column. There are multiple Xs)

    Table 2
    (the reverse pivot). In my code it is called 'Data Table'


    Unique Key - Role (now as a title). I filtered and removed all blanks. Note that the same unique key will appear for each role that that user has


    Goal


    I want to add the Unique ID's personal info from Table 1 for each Unique Key.


    Re: vba copy outline/column groups to all worksheets


    Thanks for the suggestion - that works! But it is very very slow if I basically copy/paste the formatting of the entire worksheet.


    I've tried to narrow down my range to sped it up, but keep getting errors. where am I going wrong?


    Hi Gurus,


    Im looking for help in how to write VBA macro which will copy the column groups from one worksheet and apply them to all other worksheets in the workbook.


    "List" worksheet has the same columns as every other worksheet in the workbook, I have used the group feature to allow me to easily expad and collapse several columns. There are about 10 groups across columns in this worksheet.


    Every other worksheet in the workbook has the same columns (they were created with another macro as a copy of "List"). I want the same groupings applied to each of them.


    I hope thats clear! Thanks in advance for any help!


    Cheers,
    Tim

    Re: VBA to join tables with unique key in first column


    Awesome! Thank you Mumps so much for the help!


    I modified the code so that it was not hard coded to the copy columns where ColCount was defined as the last column of the merge from sheet range.


    Code
    Worksheets("merge from sheet").Cells(foundID.Row, 2).Resize(, ColCount).Copy Sheets("data table").Range("D" & ID.Row)

    Hi,


    I am trying to write a VBA script which will copy over a dynamic number of rows and columns from one table to another table based on a unique key in the first column of each table


    I have attached an example file which has the two tables I would like to join. The 'Data Sheet' would start with just the three columns (unique key, manager, and role) and only ever have 3 columns. The 'merge from sheet' has the unique key, and more info about the employee (there is not a set number of columns here, but it will always be bookended by the unique key columns). There is also a matrix on that sheet which I do not want to include in the merge.


    [TABLE="width: 395"]

    [tr]


    [td][/td]


    [TD="colspan: 4"]Add to manager_info table[/TD]

    [/tr]


    [tr]


    [TD="align: center"]Unique Key[/TD]
    [TD="align: center"]Employee[/TD]
    [TD="align: center"]Salary[/TD]
    [TD="align: center"]Number[/TD]
    [TD="align: center"]Hire Date[/TD]

    [/tr]


    [tr]


    [TD="align: center"]1[/TD]
    [TD="align: center"]Bobby[/TD]
    [TD="align: center"]$10[/TD]
    [TD="align: center"]3234[/TD]
    [TD="align: center"]1/1/2014[/TD]

    [/tr]


    [tr]


    [TD="align: center"]2[/TD]
    [TD="align: center"]Rockey[/TD]
    [TD="align: center"]$11[/TD]
    [TD="align: center"]3245[/TD]
    [TD="align: center"]2/2/2014[/TD]

    [/tr]


    [tr]


    [TD="align: center"]3[/TD]
    [TD="align: center"]Tommy[/TD]
    [TD="align: center"]$10[/TD]
    [TD="align: center"]3256[/TD]
    [TD="align: center"]2/4/2014[/TD]

    [/tr]


    [tr]


    [TD="align: center"]4[/TD]
    [TD="align: center"]Billy[/TD]
    [TD="align: center"]$20[/TD]
    [TD="align: center"]3267[/TD]
    [TD="align: center"]4/5/2015[/TD]

    [/tr]


    [tr]


    [TD="align: center"]5[/TD]
    [TD="align: center"]Mindy[/TD]
    [TD="align: center"]$100[/TD]
    [TD="align: center"]3278[/TD]
    [TD="align: center"]10/10/1950[/TD]

    [/tr]


    [/TABLE]




    Data Sheet: [TABLE="width: 500"]

    [tr]


    [TD="colspan: 3"] Before Macro is Run
    [/TD]
    [TD="colspan: 4"]Added from Macro
    [/TD]

    [/tr]


    [tr]


    [td]

    Unique Key

    [/td]


    [td]

    Manager

    [/td]


    [td]

    Role

    [/td]


    [td]

    Employee

    [/td]


    [td]

    Salary

    [/td]


    [td]

    Number

    [/td]


    [td]

    Hire Date

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1
    [/TD]

    [td]

    Bill

    [/td]


    [td]

    Builder

    [/td]


    [td]

    Bobby

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3234
    [/TD]
    [TD="align: right"]1/1/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]1
    [/TD]

    [td]

    Bill

    [/td]


    [td]

    Fixer

    [/td]


    [td]

    Bobby

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3234
    [/TD]
    [TD="align: right"]1/1/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]1
    [/TD]

    [td]

    Bill

    [/td]


    [td]

    Cleaner

    [/td]


    [td]

    Bobby

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3234
    [/TD]
    [TD="align: right"]1/1/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2
    [/TD]

    [td]

    Ted

    [/td]


    [td]

    Doer

    [/td]


    [td]

    Rockey

    [/td]


    [TD="align: right"]$11
    [/TD]
    [TD="align: right"]3245
    [/TD]
    [TD="align: right"]2/2/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]3
    [/TD]

    [td]

    Loise

    [/td]


    [td]

    Shopper

    [/td]


    [td]

    Tommy

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3256
    [/TD]
    [TD="align: right"]2/4/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]3
    [/TD]

    [td]

    Loise

    [/td]


    [td]

    Planner

    [/td]


    [td]

    Tommy

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3256
    [/TD]
    [TD="align: right"]2/4/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]3
    [/TD]

    [td]

    Loise

    [/td]


    [td]

    Fetcher

    [/td]


    [td]

    Tommy

    [/td]


    [TD="align: right"]$10
    [/TD]
    [TD="align: right"]3256
    [/TD]
    [TD="align: right"]2/4/2014
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]4
    [/TD]

    [td]

    Kate

    [/td]


    [td]

    Driver

    [/td]


    [td]

    Billy

    [/td]


    [TD="align: right"]$20
    [/TD]
    [TD="align: right"]3267
    [/TD]
    [TD="align: right"]4/5/2015
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]5
    [/TD]

    [td]

    Rothgaur

    [/td]


    [td]

    Security

    [/td]


    [td]

    Mindy

    [/td]


    [TD="align: right"]$100
    [/TD]
    [TD="align: right"]3278
    [/TD]
    [TD="align: right"]10/10/1950
    [/TD]

    [/tr]


    [/TABLE]





    I have found and adapted some code which does a vlookup to bring just the first column. I thought about adapting with a loop to add each column row by row, but I think that would be very time consuming...a match, select and copy/paste may be faster/lighter? Maybe there is another way I don't know about (collections or something)?


    Note: This will be part of a series of macros which convert the matrix (employees names linked to roles with the X) into a pivot table, the end goal being to have a list of Managers, their employees, and which roles they have.


    Thank you so much for any help you can give!!



    forum.ozgrid.com/index.php?attachment/64720/

    Re: VBA to search range for forumlas and copy down to last row, copy down all formatt


    Thanks Ger,


    Mind if I ask you a few questions to disect the code?


    So when you define R - you are defining the whole table


    Defining x is just the header row


    Using Set X = intersect (r, r.rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn) is what is used to define that header row (row B9:BE9) right?


    Then when you say x.filldown you are filling down all the forumlas
    r.rows(1).Copy grabs the formatting from the first row
    r.PasteSpecial xlPasteFormats then pasts down all the formatting for both the formula cells and the value cells correct?


    That seems like it does the trick. I'll experiment and get back to you if I have any other questions.


    Thanks!

    Hi Excel Gurus,


    Running into a bit of trouble writing some VBA code to do 2 things:
    1) search a range (the first row of my table) for all cells that have forumlas and copy them down the column to the last row of the table
    2) reset all the conditional formatting for the table based on the conditional formatting in the first row of the table down to the last row of the table.


    Some considerations
    1) there are hidden columns (not sure if that matters)
    2) Excel 2010


    Background:


    I have a table with data in rows B9:BE200. The first row of the table is protected to preserve the formatting and forumlas. In that first row (B9:BE9) some cells have forumlas, some have values, and some are blank. The rest of the rows in the table can be changed. Deleting a row is often necessary, as is adding a row but it creates #REF errors. I'd like to create a macro that in one click will go to the protected first row of the table and 'reset,' if you will, all the formulas down the columns (ignoring the cells that have values or are blank). After that is complete, then reset all the conditional formatting for the table based on the formatting of the first row.


    I know there are some good VBA functions like '.hasformula' but am not sure how to use them in a loop that satisfies the above.


    Thanks!
    Tim

    Re: Macro to use vlookup or match/offset to fill out a table


    Ok awesome - Thanks for that, and makes sense. I'll use the formula format that you suggested. I'll then copy everything and paste values into a new sheet. This 'pre-mapping' will then get distributed to 20 or so users to add additional 'X's in the boxes which is why I didn't want formulas in there.


    Now I just have to figure out a good way to merge the 20 or so iterations of this that I'll get back after I distrubute it :)


    Again thanks for the help! I am still curious about how I'd write a macro to do a looping VLookup which would go across multiple columns and then down to the next row. I found some information in Example 3 here (http://www.exceltrick.com/form…okup-in-vba/#comment-1096) but its just for one column.

    Re: Macro to use vlookup or match/offset to fill out a table


    Hey Herbds7 - thats neat! But for some reason when I try to change fill in my real data it says "itilization of the data source failed."


    My intended use for this is to embed it in an already existing table - is the concensus that its either too difficult or really not a good idea to write a macro to do this?

    Re: Macro to use vlookup or match/offset to fill out a table


    Hi Ger,


    Thanks so much for your help! There are a couple reasons I want to do this with a macro. The first is that I don't want there to be formulas in each of the cells. I'm creating a list that needs to be validated and changed by many managers but wanted to provide them a starting place or pre-mapping if you will.


    The other reason is that my table will be ~2,000 rows down and 70 columns. I think that many vlookups will crash Excel.

    Hi,


    I’m trying to write some code which that will essentially act as if I had a vlookup in every cell in a range. The lookup value is different though for each row. It may be better to use a Match/Offset combo but I’m really not sure.


    There are two tables. The first table (Table 1) has different job titles in Column A. Columns B-D contains different Roles in Row 1 (“Role 1”, “Role 2”, “Role 3” etc.). The body of the table is filled with ‘X’s in various cells which indicate which roles are part of each job.


    For example:


    Table1

    JobRole1Role2Role3
    Job1XX
    Job2XX
    Job3XX



    The second table is much larger and adds a column ‘Name’ before the Job column. Multiple people can have the same job.


    Table2

    NameJobRole1Role2Role3
    TomJob1
    DickJob2
    HarryJob1



    The macro I’d like to write populates the empty cells in Table 2 with the correct ‘X’s based on the roles that are attached to each Job in Table1. It would start in Column C, Row2 on Table 2, and look for Column 2 Row 2 (“Job1” in this example) in Table 1, then paste whatever value is in the cell with the same Job/Row cell. The macro would then move onto the next column in the row. When it has copied all the cells in that row, it moves to the next row.
    The end result would look like this:


    Table2

    NameJobRole1Role2Role3
    TomJob1XX
    DickJob2XX
    HarryJob1XX



    I’ve seen a lot of similar posts but cant quite zero in on what exactly I need for this to work. Any help is much appreciated!
    If it matters I’m using Excel 2007 and am a pretty novice VBA coder. I’m good at adapting code, but can’t really write it very well.


    Thanks!
    Tim

    Re: VBA code to copy sheets of just values and combo boxes in new workbook


    David,


    Thank you so much for your quick response. I'm afraid I'm such a novice with VBA, I am struggling to apply your wisdom. Would you mind merging your solution in my code so I can see where it fits? I want to make sure to preserve the formatting etc. in the new workbook, but make sure just to past the values rather than the formulas.


    Thanks!!
    Tim

    Hi!


    I have written some code (adapted from another post) which will create a new workbook and then copy several sheets of just the values in the old workbook into it. The code also keeps the formatting (which is GREAT!) but it seems to fail to copy/paste the combo and check boxes that I have on the old workbook. any idea how to include them? ALSO the code for the string "NewName" returns the error : "Method 'Range' of object '_worksheet' failed. What am I doing wrong? Custname, Ploc, and wtgtype are named ranges in the worksheet 'Project Info' in the old workbook.


    Thanks!
    Tim