Automatically update existing working VBA tab naming code to make it execute without having to click into every sheet

    Hello all,


    I am wondering if anyone could please kindly help me


    I have received loads of help from a very kind and clever helper called Carim but I am stuck at the last hurdle with the following.


    I have the below VBA set into each sheet of my 40 sheet workbook and as I fill in the cells next to the Plot Number cell on the data input sheet (the Cells I am referring to are coloured in green for your reference) the information I need for each sheet name builds in columns Q and S on the data input sheet. This information is then added to the plot sheets in cell H9 via a code as follows. Cell H9 has the following formula


    ="Plot "&'Data Input'!Q2 ="Plot "&'Data Input'!Q3 ="Plot "&'Data Input'!Q4 etc


    The code can been found in each plot sheet in cell H9 but the formula has been coloured in white so that it doesn't show on the finished PDF, this information is then used to name the worksheet tabs accordingly.


    It all works except one final issue which is the VBA does not execute unless I click into each sheet then click anywhere within the sheet.


    EG if I type my name in the first green cell, Cell D9, nothing happens to the tab named Plot 1 unless I click into the sheet then hit return or click elsewhere within the sheet, then it updates the tab name to read whatever I have typed in to Cell D9, D30, D51, D72 and so on.


    Does anyone kindly have a solution they would please share with me to automatically update the VBA in each tab without clicking into them. I have attached a sample sheet


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set Target = Range("H9")

    If Target = "" Then Exit Sub

    Application.ActiveSheet.Name = VBA.Left(Target, 31)

    Exit Sub

    End Sub


    Many thanks for taking the time to read this


    Kind regards


    Debbie

    When do you want it to update?


    You don't need the second Exit Sub


    I also, cannot see why you need the formula in Q2 of the sheet to create the name. Do you have to change H9 for each new sheet?


    Without activating the sheet the code will not know which one to rename.

    Hi royUk,


    Thanks for getting back to me.


    I would like it to either update as I type the plot number in each cell coloured green or to be able to press a button once I have completed all the plots on the data input sheet that I have information for.


    Yes that is correct I have to change H9 for each new sheet. The whole program is to note down information on separate flats within a building site. the flats are known to me as the Plot number hence why the sheets are named by plot but PDF need to show the actual address which is used to name the PDF.


    I have the code in each sheet under right click, view code and the formula is always the same, to use the information found within that sheets in cell H9. Due to this information being different for each sheet I made the formula on the data input sheet Rows Q and S so that the cell ref could always be the same code when renaming the sheets i.e. sheet name = H9. My apologies I am not extremally experience at this and couldn't think of any other way to do it.


    Many thanks

    Debbie

    Fair enough. Mine was probably overkill anyway as I was creating new sheets if they didn't exist and all sorts ^^


    Good luck with your project :)

    Hello Debbie,


    Let me tell you it was ' fun ' to try to fix all your issues ...;)


    Hope the last version is in line with your expectations

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login