"Save As" Automatically Using Value In A Cell
Jul 7, 2006Could any one help me with the VBA code needed to automatically save a workbook (after click a button) under a file name prefix from one of the cells on the worksheet.
View 9 RepliesCould any one help me with the VBA code needed to automatically save a workbook (after click a button) under a file name prefix from one of the cells on the worksheet.
View 9 RepliesI would like excel to automatically suggest the save as file name and location for my workbook when I click the save, save as or close options. The file name should be a combination of values in cells A1 and A2 of Sheet1. The location should be a folder named "Bill" in C:/My Documents. I think I can do it with a beforesave code but I am new to vba.
View 13 Replies View RelatedHow to click a button to automatically combine a few cell data and save it as the file name ?
The file name i want to save, is in this method.
( Date 2013-07-22 ) + ( Cheque No UOB000000 ) + ( ReceiverName ) + ( InvoiceNo L123456 ) + ( Payment Voucher Code ) + ( Amounts $1111111 )
______V5___________________E17___________________D11________________O9______________Add word ( PV )_____________W16
Sample File name : 2013-07-22_UOB000000_TransAuto_Inv778899_PV_$$$$$$$
I tried to add this code : FName = ActiveWorkbook.Sheets("Payment Voucher").Range =("V5&"_"&E17&"_"&D11&"-"&O9&"_"&"PV"&"_"&W16")
But getting error.
Than i tried to group all the cell data into cell (E11) and than select as range (E11), but when i tried to group it, the beginning the file name instead of i want it to be the date 2013-07-22_UOB........ it become 41113_UOB............
I have a range that refer to an external data. This external data is refreshed every one minute. So the data is changing every one minute. I need to copy the content of one fix cell in that range into another cell every one minute, each time copy to a different cell. Example: cell A1 has the content that refer to an external data. Cell A1 is updated every one minute. At first A1= 100, I need it to be copied to cell B1 (so B1=100); one minute later, A1=101, I need it to be copied to B2 (so B2=101) and so on.
View 4 Replies View RelatedI am very new to vba and trying to figure out an auto save macro that will automatically save my workbook when a certian cell range is changed. Right now I have a macro set that automactially record the time and date of when a change is made to the name cell, I want to set up a macro that will automatically save the file when the time is updated.
This program is used by several users and they have a tendnecy to forget to save the program so that when other people want to check the updated data nothing has changed because the changes have not been saved. I have attached the file that I am working on. When a change is made in column F then Column G automatically updates, now I want column G to trigger autosave. I would also like a msgbox to appear to tell user that file has been saved.
Is it possible to have the worksheet save itself automatically every 5 minutes or so?
I don't want the user to have to click on a macro or do anything. Basically they will not even know that it is saving.
I would like to save a spreadsheet at a certain time every day.
View 5 Replies View RelatedI am trying to fix is that I have two computers networked together both with multiple users all with access to the report form that I am using which is in the shared file. If I have been working on it (I am still trying to do improvements when I have time), and I have to leave for a fire or forget to close the report, no one can access it other than with read-only.
My idea was to run some type of code that either when the screen saver comes on or after so much time with no activity, it would close and save the file with a new file name(just in case I don't want the changes that I have made in the code or something).
I have a workbook with 4 worksheets the first is called "input" which I use to enter information which goes to the other worksheets which arecalled engine, gearbox, doors.
I am stuck on the following; after inputting the information. I want to save as pdfs in various locations.
Is it possible to have 4 buttons on the input sheet which automatically :
Button 1 - Save "engine" worksheet as a pdf to a specified folder
Button 2 - Save "gearbox" worksheet as a pdf to a specified folder
Button 1 - Save "doors" worksheet as a pdf to a specified folder
Button 1 - Save the sheets as multiple excel files to a specified folder
I've created a MsgBox that if you do not respond or Click on it within a set time it will either disappear or perform an action upon no selection. It works great....IF it’s the only excel workbook open. If there is a second workbook open (Even if the second WB is minimized) then the MsgBox just stays up and the "Timer" no longer works. My Code is as Follows:
Sub TimedMsgBox()
Dim Box As Object
Dim varResponse As Variant
Dim Res As Long
Set Box = CreateObject("WScript.Shell")
varResponse = Box.Popup(Text:="Which button will you click?", secondstowait:=5, Title:="Click Me!", Type:=vbYesNoCancel)
If varResponse = vbYes Then
MsgBox "You clicked Yes"
ElseIf varResponse = vbNo Then
MsgBox "You clicked No"
ElseIf varResponse = vbCancel Then
MsgBox "You clicked Cancel"
ElseIf varResponse = -1 Then
Res = Box.Popup(Text:="What? Do you not like me?", secondstowait:=5, Title:="You Didn't click Me!", Type:=vbOK)
End If
End Sub
Our company has designed a Excel Document for quoting purposes. The problem we are having is that certain sheets that are needed to do the quote do not need to be used after the quote is finished and saved. These extra worksheets are taking up a lot of room on our server. In the document I have already disabled the save feature so that the saveas has to be used. Is there code that can delete sheets 3, 5, 6, 7, 8 upon doing a save as?
View 4 Replies View RelatedI want to write a piece of code that will automatically do a SaveAs on the workbook I'm using, say every hour, and add the current system date and time to the filename every time.
View 4 Replies View RelatedI need a macro that will create a backup copy of excel file in other folder when an original file is closing.
View 6 Replies View RelatedI'm not sure if this is possible but I want to be able to click print and after it has printed, transferred and cleared I would like it to save the purchase order and continuation sheet.
If this is being done on different computers is it possible for it to simply bring up the dialog box and the user save it where they would like?
Libby
Is it possible to open a template, prompt the user to enter data and have the template save itself as the user defined data?
View 5 Replies View RelatedI am in the process of setting up an Excel invoice. So far I have managed to implement the following:
On open: Incrementing invoice number
Cells cleared ready for next use
Private Sub Workbook_Open()
Sheets("Sales Invoice").Range("H2").Value = Sheets("Sales Invoice").Range("H2").Value + 1 .....................
There is only one task left I need to automate. On closing the document I would like a copy to be saved in a specific folder and be given the file name of a cell (B9). Obviously I would not want the 'saved copy' to retain the Incrementing invoice number or the clear cells function. Even if the invoice copy was exported as a flat jpeg that would be fine. I am using Office Exce 2003 (Sp2) - I have attached my invoice should anyone be kind enough to take a look.
I'm trying to create a sub that will save my worksheet to a tab delimited text file anytime there is a change in the worsheet data (all cells are linked to cells in other workbooks). I've figured out the command to save the file
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsChrisMy DocumentsBook1.txt", FileFormat:=xlText _
, CreateBackup:=False
but I'm not sure how to get a sub routine to start running when the file opens and to have it run continuously while open. I've found the command:
Application.Volatile
that will flag when any cells in my range are recomputed and run a function, but functions don't allow me to save the file.
I have put together a booking spreadsheet which uses a UserForm. see attached worksheet. When you start the UserForm and select a name, date, number of nights, and then 'Add', the data is added to the worksheet.
1. My first problems is that when I select the 'Cancel' button on the Userform, I would like the spreadsheet to automatically save under C:My Documents. And also save the file as 'Marketing_DDMMYYYY.xls' as todays date.
2. My second problem is that the 'Leaving Data' column sometimes shows the leaving date in American format (MM/DD/YYYY), and sometimes English format (DD/MM/YYYY).
How can I make this always use English format?
I have an excel file that I use to track all my financial investments. The file is already set up with a query to retrieve stock prices when I refresh the document.
I would like to be able to open up the document, and store the closing price of the stock(s) with a date in the next column over. With this information I can graph weekly, monthly annually, or any time period I want.
I have been searching for a couple of hours now, and I could not find anything similar to this. I would think this is a pretty common idea for many people tracking their investments with Excel, so if it has been covered perhaps someone can point me in the right direction.
I don't have any programming experience in excel so as detailed of an explanation as possible
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
View 9 Replies View RelatedI am using Excel 2007 and have to send monthly payslips to respective email ids. I have the Name and Email ids in Sheet 2 Range B1:C59 , and employee codes in A1:A59. And in Sheet 1 i have the Payslip format which was automatically displayed when i select the employee code from drop down list in the cell E7 in sheet 1.
Every time i manually save the files as PDF and send to their Emails. I want the out put as whenever i select the employee code from drop down list , it should automatically save as PDF with Name of that employee ( Name of the employee is located in B1:B59 in Sheet 2 ) and attachment should automatically go to that employee's email Id.
I presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
Here is the current
Sub SaveIt() ...
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
I have a VB set up that "Save As" the SHEET, with name based on the contents of a particular cell. - which works perfectly.
I then have another VB setup to save new file as a PDF.
The issue I'm coming across is that it is saving the PDF as the original excel workbook's name, not the new "Save As" name
example
Sample.xls has contents in Cell A1, when Macro activated it saves the active sheet as a new file with the contents of A1 as its name. Lets say Bob.xls
Sample.XLS also has another Macro, which when activated, saves as a PDF.
This macro is carried over to the newly created bob.xls
Issue is, it is saving bob.xls's PDF, as sample.pdf.
Anyway I can get it to save as bob.pdf without having to create a new macro every time?
I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:
On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies View RelatedWhat I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.