Optimising vba

  • I need some help cleaning up VBA and adding some new features if possible.
    I don't have a lot of experience, so I've bodged different code together that I've found online.

    I'm sure anyone who looks at my code will see that it's quite tragic.


    Basically I've created a directory worksheet to control the remaining worksheets in my workbook.

    In the directory I have some macros that can hide / show the other worksheets, and also rename them when certain cell values change.

    I have very limited VBA experience, so the code I've used is really clunky and slow.

    Would someone be able to have a look over it and recommend how to speed it up?

    I've no idea where to start.


    I'm also trying to include two more features in the "Directory" but don't know how.

    1. I'd like to enable / disable auto calc for specific sheets, if a certain cell says "Yes".

    2. I'd like to copy the contents of a template sheet to other sheets if a certain cell says "Yes".


    It might sound weird, but it'd all make sense when you open the attached spreadsheet.


    Thanks

  • Rather than trying to analyze your existing code, it might be easier to help if you could describe in detail what you want to do step by step referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks for the replies guys.


    My biggest issue is with how slow the macro runs as it is checking each cell to see whether a change is required or not.

    The original spreadsheet is very big with a lot of calculations running at the same time. I've excluded it for simplicity.

    The idea of using the directory tab, is to speed things up and make the spreadsheet easier to follow.


    I am trying to do 4 things with the workbook.

    1. Rename worksheets based on value in col "B".

    2. Copy a template worksheet and overwrite the contents of other sheets if the cells in range G10:G29 say "Yes".

    3. Show / Hide worksheets based on whether Col "I" says "Yes"

    4. Enable / Disable autocalc on specific sheets if Col "K" says "Yes"


    1. The renaming of the worksheets seems ok as I found a code online that looks compact and neat.

    A2:A39 contains a list of all the worksheets in the workbook. A2 = "Sheet1", A3 = "Sheet2" etc...

    The adjacent range in column B is is used to rename the worksheets.

    So if I write B3 = "SheetB3" and run the macro, the worksheet "Sheet2" will be renamed "SheetB3".


    2. I don't know any elegant way to copy a template and overwrite a specific sheet when a cell says "Yes".

    I tried naming the cells "Overwrite_Sheetn"creating a macro based on the code below (to overwrite 20 sheets), but it doesn't work properly.

    For some reason Sheet10 won't copy the template file.

    Again, this is a slow way of running an already intensive spreadsheet.

    3. Hide / Show tabs is my main culprit for slowing everything down.

    I renamed individual cells "Show_Tab_2" all the way up to "Show_Tab_38".

    If one of these cells changes, the respective tab hides/shows based on the code snippet below.



    4. Enable / Disable autocalc was purely to speed up the spreadsheet some more.

    A lot of the spreadsheet contains placeholder formulae that doesn't need to be calculated all the time.

    I'd like to have the ability to switch this on/off for specific sheets.


    I tried using the following code in each respective sheet, but it doesn't work.

    Code
    1. Sub Calc()
    2. If [Calc_Tab10] = "Yes" Then
    3. Call TurnOnCalc_Tab10
    4. Else
    5. Call TurnOffCalc_Tab10
    6. End If
    7. End Sub

    I've attached my most recent version of the spreadsheet with all of my changes.


    I hope this makes sense. Thanks

  • I have a few questions for clarification.

    1. Column A uses formulas to insert the sheet names. Are the formulas necessary? It would help if the sheet names in column A were simply entered manually without the need for the formulas.
    2. Rather than renaming all the sheets based on the input in column B all in one go, would it work for you if the renaming is done automatically as you enter a value in column B?
    3. Similarly, to copy the template, show/hide sheets and enable/disable autocalc, would it work for you if these actions are also done automatically when a value is selected in column G, I and K ?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 1. I don't think there would be an issue with this.

    2. Would this be slower as the workbook is monitoring any changes to the cells?

    3. I did have it like this in a previous version of the sheet but thought it was slowing the sheet down for the same reason as #2.

  • Try the attached file. Before you do anything, save the file, close it and then re-open it. The reason for this is that there is a Workbook_Open macro in the code module for ThisWorkbook that populates column E each time you open the file. I have deleted most of your named ranges and the formulas in columns A and E as they are no longer necessary. Make an entry in column B and press the RETURN key to rename the sheet and re-calculate column E. Make a selection in columns G, I and K for the appropriate action. I hope I have covered everything but please let me know how it works out.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you Mumps for having a look over this.

    I really appreciate your help, and apologies for all the inconvenience.


    I've tested your example workbook and looked at your code and it's very tidy; however, I think there's a misunderstanding of what I'm trying to do.


    Not all sheets will require the ability to copy the template file.

    For clarity, I've renamed the sheets that need to be overwritten with the template file (Target 1 - Target 20).

    Copying of the template file, copies the contents and pastes it into cell A1.

    The actual spreadsheet that I want to use the directory for has thousands of calculations. The template worksheet has 4448 formulae, 5133 conditional formatting instances & 677 data validation instances (It's a beefy & slow spreadsheet). I'd like to copy the template sheet over identically (inc formulae, conditional formatting, data validation etc.).


    I don't want multiple functions to run simultaneously.

    i.e if I change the cell name in Col-B to rename a worksheet, only the renaming of that sheet would occur.

    At present, if the cell is renamed, the template is copied over too.

    Similarly, if I enable auto calculations or if I decide to show / hide a tab, those functions would execute separately too.


    The manual & automatic calculation function doesn't seem to work.

    Perhaps I haven't used the workbook properly, but I tried changing it for different sheets and they all showed as being manual.


    The trigger used to run the macro also has a limitation to bulk actions.

    Say I want to hide 30 tabs, I would have to click through each tab separately.

    It would be more convenient to copy/paste the value in the cell and click a button to run.

    It's the same for renaming tabs. I renamed a tab "Target 1" and dragged the cell corner down to increment the names, but this triggered an error.


    I've reformatted the workbook so that visually, it is easier to interpret what I'm trying to achieve. I've also updated the vba accounting for the changes in columns.

    I added in an extra column to enable/disable conditional formatting at the end. Once I discovered how many conditional formats were active, I realised that this could be a big influence on the speed. I can do this using a manual process but it gets really messy when copying over the template files.


    Do you know if its possible to use vba to switch the conditional formatting on/off.


    Thanks

  • Not all sheets will require the ability to copy the template file.

    If you have the drop down in column F only for rows 10 to 39, that would restrict the copying of the template only for those sheets. The way you have it set up now, should work the way you want.


    Rather than copying the contents of the Template and pasting it in the other sheet, why not delete all the "Target" sheets and then make a copy of the template sheet and rename it based on the value in column A. This would produce an exact copy of the Template with the new name with all the formatting and formulas. Would this work for you?


    When you change the cell name in Col-B to rename a worksheet, only the re-naming should be occurring not the copying of the Template. This is the way it works form me when I test it. If you want to copy/paste values to rename multiple sheets and hide/unhide multiple sheets at one time, I can modify the code to do this. However, you would not be able to use the drop down list in column G to hide/unhide. You would have to enter "hide/unhide" manually. Is this what you want to do.


    After doing some research, i found that the manual & automatic calculation function won't work properly. I found the explanation rather confusing so I don't think that I can get that to work. Sorry. Also, I don't think that it is possible to switch the conditional formatting on/off. It might be possible for a few conditional formatting rules but you have several thousand. If it were possible, it would have to be for the whole workbook not individual sheets.


    With the large number of conditional formatting rules and formulas that you have, the macro speed will definitely be affected no matter what we do.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.