Posts by debbie bam
-
-
-
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
-
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
-
-
Hi Carim, again
I have managed to get the program working on my friends PC by indeed changing the file saving path, still can't find the route naming path in the Mac but I am not too worried as the Mac is set up differently and all the page setting are out so we will only use the program on the PC and laptop.
Regarding re-naming the sheets. I currently have a VBA code that names the tabs/sheets but only updates if I click into the sheet, this is my VBA code
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
in answer to your question, I have created a master workbook to have 40 sheets, wew open the master, save as a job name and use, if we need more then we go back to the master and save another workbook by job name part 2 etc. sometime we wont need all 40 sheets but I am happy to let the program run and delete what isn't needed if that simplifies things.
I am just loading your Sub RemanePlots script now to see what result I get
Thank for sending it to me
I would love to say thanks via the icon but I don't have the icon in the bottom right hand corner just a black triangle with an ! in side it, sorry
-
Dear Carim,
Just finished my morning meetings and catch up work and I am about to start looking at the codes that you have very kindly written for me.
In answer to your question, I guess you must be regretting answering my first question as I am not the most professional up on my lingo person to chat with and my query has not been straight forward and has taken a lot of messages
That said I am most grateful that you have taken the time to help me, you are very kind
-
You are going to regret the day you started talking to me lol
I was just showing my college the program and for some reason the macro only works on my PC, not her laptop or the Mac. Is this to do with the saving naming convention?
Screen shots attached of Mac error messages
Have a wonder rest of the week and thanks for your help thus far
Debs
-
-
-
-
-
Hi Carim,
It should be I just added the VBA to name the sheets
Is it no good? I just checked at my end and if I type in the data Input sheet only cells D9 - D30 - D51 etc when I go to the tabs Plot 1, Plot 2, Plot 2 etc once I have click elsewhere in the sheet the tab name changes.
The VBA pulls through from Cell H9 on each of the tabs named Plots
Kind regards
Debbie
-
The thanks all belongs to you, its 100% working now
Honestly it is so kind of you to take the time to help
Have a wonderful day
PS I don’t suppose you know how to make my VBA code rename the tabs without me having to click into them? On my test sheet if I change the plot numbers on the Data input sheet (I.e. Data Input sheets cells D9 – D30 – D51 etc) they rename the tabs as per the new plot numbers but only if I click anywhere within all the corresponding the sheets.
Serious, its not a big problem and no worries if your busy, I have already taken up too much of your time, don't want to be greedy
Thanks again
Debs
-
-
Dear Carim,
You genius. I am working off both a Mac for Emails and using a PC to building this program and to talk on this site. The email on the mac just said (code, 8 lines) which confused me but I have found your code and just a moment ago run the test sheet you kindly altered and that worked.
I am just opening the actual version to add this script and try running it to make sure it works on the live version but so far so good
-
Sorry, trying to do too many things at once as I am on my own in the office. It was this script from Carim which ran but nothing appeared in the Dropbox
- Sub SaveWorksheetAsPDF()
- Dim ws As Worksheet
- On Error Resume Next
- For Each ws In Worksheets
- ws.ExportAsFixedFormat xlTypePDF, "C:\Users\lucy\Dropbox\certs\" & ws.Range("D9") & ".PDF"
- Next ws
- On Error GoTo 0
- End Sub
-
Apologises I didn't see all these messages until a short while ago I was looking to my e-mail for answers
royUK
With your first script nothing appeared in dropbox
Second script gave an error message as follows
Compile error:
Method or data member not found and it highlighted the piece of the script that said .TempSht.copy
Third script gave an error message as follows
Compile error:
Ambiguous name detected: SaveWorksheetAsPDF
Carim
I am unsure where to insert the piece of the script that you have sent me (Code, 8 lines)
I have tried it in a few place but cant get it to work
Thank you both for your help though, it most kind of both of you
-
Ops returned my post too soon there
Plot 1 This would return a saved PDF called 20 Debbies place
Plot 2 This would return a saved PDF called 21 Debbies place
Plot 3 This would return a saved PDF called 22 Debbies place
Plot 4 This would return a saved PDF called 23 Debbies place
Plot 5 This would return a saved PDF called 24 Debbies place and so on
I have 40 plot per work sheets and ideally I would like the PDF's to start saving at the first plt tab and end on the 40th but if this is not achievably then I am happy to have all sheets saved as per cell D9 and delete those which are not needed.
Any help you can give me would be so appreciated
Kind regards
Debbie
-
Dear Carim,
thank you kindly for your e-mail.
I have created a blank version of my workbook and attached it as per you suggestion.
If possible I would like a code to save the tabs as per the info in D9
Plot 1
Plot 2
Plot 3
Plot 4
Plot 5
ect as per the information contained in cell D9 of each sheet so the final result is the workbook remains looking and functioning as it is but I have deparate PDF's of each sheet that if the code works will come out as 20 Debbies place,