Hello, i am trying to make a click button save a file based on the info in a cell, but it opens the saved file up and shuts down the original. i would like it to keep the original open and save the new one in the background if that makes sense? I would like a message box to appear saying "saved successfully" too.
I have used this:
Please Login or Register to view this content.
Last edited by AliGW; 01-28-2021 at 05:29 AM.
You could use SaveCopyAs but the saved copy would also have to be in macro-enabled format. Other alternatives would depend on the nature of your workbook - perhaps copying all sheets to a new workbook would work?
Anyone who confuses correlation and causation ends up dead.
thanks for the reply. a new workbook would be fine, as long as it closes down so i can keep editing the original sheet. I would also like a message box letting me know it has been saved. i just cant get my head around it....
is it possible to save a tab in the workbook as a pdf too?
Yes, using Activesheet.exportasfixedformat.
You haven't really explained what the scenario is - that will probably determine what the ideal solution is. Activeworkbook.Sheets.Copy will copy all the sheets in the active workbook to a new workbook that you can save, but there may be problems if you have links between sheets, or defined names for example.
Is the code in the workbook you are saving, or in a different workbook?
Sorry, i'm terrible at explaining things...
i want to input data into a sheet, then at the end press a button that will save a copy of the workbook im using by the filename in cell c7, to the folder on the desktop and for my original to remain open and the copy to close completely and a message box to confirm its saved. it doesnt matter if the copy is macro enabled. its just for information. can vba code also be written in to export a tab as a pdf, all in this one button click?
Do you want the data that you entered to still be in the original workbook? I assume not, but want to be sure.
I would personally suggest you have a master workbook (or an add-in) with the code and buttons/menu system in it, and a separate template for the data. You'd then press a button in the master to create a new workbook from the template, fill it in, then press another button that saves and closes the new workbook. You can then just repeat that as required.
yes, i did want to keep the original data as some of it will be used again.
Then your simplest option will be to save the workbook in the same format as the original, so you can use:
Please Login or Register to view this content.
that works great, thanks a lot. is there a way to have a message box appear to say it has saved successfully and can code be written in to export and save a tab of the workbook as a pdf when the button is pressed?
You'd just add something like:
Please Login or Register to view this content.
im getting an error message for below:
does it also save the pdf as cell c7 filename?Please Login or Register to view this content.
Last edited by confused monkey; 01-28-2021 at 04:07 AM.
No, you need to add the filename to the end of the path for the PDF.
If you get an error, it helps to tell us what and where...
Also, please use code tags when posting code.
sorry. ive just put '& Range("c7").Value' after file path and it works!! thanks a lot for your help, sir.
Glad to help.
just out of interest, if i wanted to create a folder first through vba (so a folder first then file to save into) with the name in c7, how would i do that?
Use MkDir and pass the full path. All the folders above the one you creating must already exist.
im struggling a bit..... i cant figure out what the line of code should be and where to put it!
Administrative Note:
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
all done. apologies
The closing tag needs to be [/code] rather than [code]
got it. done now.
any ideas on the above, please?
Something like this:
Please Login or Register to view this content.
that works absolutely perfectly. thank you very much. Could another tab be added as page 2 of the newly made pdf or would it have to be a new file of a different name??
i triedbut its not working and i cant see where im going wrong!!Please Login or Register to view this content.
You can do it manually but I haven't found a way to do it directly in code.
And as soon as I say that, I realise how to do it! you just need to select all the sheets you want included first, then use the Activesheet.Exportasfixedformat line as before!
i was just trying something like that but alas, no joy.......
Please Login or Register to view this content.
That is not what I said. you need to select the sheets you want, and then use the same activesheet.exportasfixedformat line as you had originally.
forgive me, im such a newb. how do i select the sheets i want without specifying an array?
You do use an array:
Please Login or Register to view this content.
bloody hell, i nearly had it!! that works great! thanks a lot for all your help. this should arm me well for future projects. take care.....for now.......
Glad to help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks