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)
Many thanks for taking the time to read this