Posts by RECOChris

    Carim, you've been incredibly helpful. Thank you! Please see my responses in the quoted post.


    Considering how weird my situation is and the level of Excel expertise of those this request is intended to cater to, I'm thinking of foregoing trying to delete the CF rules. The intent was to make the resultant file size of the EstimateReporter sub smaller, but the cost-benefit ratio doesn't seem to be there.

    Additionally, as a follow up to the above post, deleting the conditional formatting rules with #REF! errors from my workbook allowed the code in post 1 to run completely:

    The function PasteFC is its own separate module:

    However, when I run the above code, 2 things happen:

    1. The result conditional formatting is not kept on some of the cells: the fill color is incorrect and the cells have a full thin outline border
    2. The conditional formatting rules are not deleted.


    #headdesk


    I'm debating foregoing trying to delete CF rules while maintaining the CF results.

    I ran the code in your test file and it ran flawlessly through all the paces I could put it through that would be applicable to my situation. I did some more digging into my file and found a couple of #REF! errors in my conditional formatting and promptly deleted the rules. However, I was still running into the Error 13 type mismatch after doing that, so I then added an

    On Error Resume Next

    to my code in post 19 before

    If Len(c) > 0 Then

    and the code fully executes, however the result is that all conditional formatting rules are deleted without keeping the result of the conditional formatting. Additionally, borders are added where no conditional formatting rules had been applied. Rhetorical question directed at Microsoft: why did you guys make borders so difficult to work with?

    I copy-pasted the code from the test file in your post into the code in my file and ran the sub:

    Hi Carim,


    *In my case* here is the border situation:


    The cell (or merged cells) will have a thick box border by default; I do not have any conditional formatting that affects borders between cells. My conditional formatting (if the condition is met) will remove the left border, the top border, and the right border. *Sometimes* the bottom border will also be removed (if the condition is met), but more often than not, it will remain. I never change border color or style. I can put together a file if it will help, but it will have to wait until morning.

    Hello,


    I have the following code (as a module) in Excel 2010 that creates a .xlsx static copy of 3 specific sheets of large macro-enabled workbook (that I can't share here) with a button click. The code also removes certain objects, all hyperlinks, converts all formulas to just values, removes all data validation, and breaks all external workbook links. The only other thing I want the code to do is to keep the effects of any conditional formatting but remove the conditions.

    I've tried incorporating the following into different places of my code, but I can't seem to get anything to work.



    Running Allen Wyatt's code as an independent module seems to have the most promise, however, when I plug in a


    Call PasteFC()


    before the


    Next ws


    line of my code, I get an error 13 type mismatch in the last case (the Expression case) of the ActiveCondition function at line:


    If Application.Evaluate(FC.Formula1) Then.


    I've also tried the responses in:



    and



    Since Wyatt Allen's code seems the closest to accomplishing what I'm after, is the error 13 the easiest to resolve? Or are there other, better venues?


    Thanks in advance.

    Hello,


    You guys are always awesome with insight into difficult excel situations so I have another one for you. I have a convoluted way that I need to lookup the value in an external macro-enabled workbook. I need to concatenate the workbook network address, a dynamically updating cell that is the sheet name, and the same cell in which data exists on each of the sheets. I can handle the concatenating function, but I can't seem to find the correct indexing or lookup function/protocol that returns the value of the concatenated string. I can't use the indirect function for looking up values in the reference workbook because the workbook has hidden sheets. If this needs to be done via VBA, I will redirect my question there. Example workbook attached.

    Return Value of Concatenated String.xlsx

    Hi Roy,


    The goal of starting with the large xltm file was so that when a user clicks the "New" button in Excel, this template will show up in their list of templates (after all machines are updated); when the user selects this template they'll be able to click the button that I will assign the code below to and generate the 3-page .xlsx file as a report, so to speak, of their work. I wasn't able to come up with any happy marriage of the 2 codes: your latest code post, and mine from post 21. I did a code-for-code run of the 2 codes on the same base .xltm file and the results were:

    • Roy's Code:
      • Time to generate .xlsx file: 00:02:10.66 (hh:mm:ss.ss). I noted that the screen spent most of this time on one of the tabs that the code unhid; there is a large amount of data and formulas on this particular tab.
      • Generated .xlsx file size: 89 kb
      • Closes both new .xlsx file and existing .xltm <-not desirable, I would like both files to stay open.
    • My Revised Code:
      • Time to generate .xlsx file: 00:00:06.64 (hh:mm:ss.ss)
      • Generated .xlsx file size: 120 kb
      • Both the new .xlsx file and the existing .xltm files remain open with the .xlsx being the active file.

    I'm willing to accept the larger generated .xlsx file size of my revised code; further refining of my code in post 21 produced the following:

    <Note> There are a few shapes/text boxes in the original xltm that I want to keep and have displayed on the generated xlsx.

    At this point, I think my requests is resolved. I really appreciate your help and insight on this.

    Roy, I'm working to see if I can merge your current code with my code in post 21. Your code produces a smaller file size than my code, but takes longer to execute (I suspect the volume of data on the hidden sheets is the culprit). The file size your code generates is on the order of about 40kb smaller than what my code, so for my purposes, that is negligible, but obviously file size translates directly to server space occupancy. I will report back sometime next week what I come up with.

    So after much tinkering with both Roy's work here and this little gem that I found, I arrived at the code below that does the following when executed:

    1. Creates a new xlsx workbook using the save-as dialog box.
    2. The new workbook is a defined set of tabs from the original workbook.
    3. The new workbook is values only:
      1. All hard-code formulas are removed, all data validation is removed, all named ranges are removed, all hyperlinks are removed, all external links are removed.
      2. In my specific case, I deleted Command Buttons and Toggle Buttons as I had a group of pictures (think company logo in a letterhead) that I wanted to keep.

    Many thanks, Roy!

    Bug somewhat fixed in that I don't get any coding errors; I removed "oWs." from that line. However, the last 2 tabs of the resultant .xlsx file still retain the formulas. I'm also getting #Ref! errors in the resultant .xlsx file for those last 2 tabs when I run the code below. So I'm wondering if the sheets need to be processed in a specific order in terms of converting the formulas to values? I ask this because the last 2 tabs of the large .xltm are almost entirely dependent on the inputs/values of all the preceding tabs.