I would like to create a routine that automates the save process, specifically I would like for the user to click file/save, and then have the save name match the value in a "job number" cell. So if the user runs the save routine, it will automatically save as cellsvalue.xls. Is this possible? and if so, would it be possible to check the value of the cell and if the cell is blank return a message forcing the user to enter a value in the "job number" cell before they could save?
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.
I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
I'm having a little trouble making this code work correctly. This macro, Once finished, is connected to a (Save) command button. Here is what is suppose to happen, First thing to remember is another sheet (Sheet1) has been populated with various information which is used as a receipt. The information came from sheet2. I need to copy sheet1 just as it is, format included, create a new empty sheet, paste the information into the new sheet, name the new sheet with the values from cells in sheet2, E3, D3, and the date formatted as the month and year only. Then I need to create a folder in the my documents folder named from the values from sheet2, E3, D3, and todays date with the current time. If the folder already exists, then I just need it to change to that folder, and save the newly created worksheet (as named above) into that folder. After the save is executed, I need to delete that new sheet from the current workbook. Then I need to return to sheet2 to the last active cell and run the last few lines of code which collects the value of the cell 1 to the right of the active cell and transfers it to the cell 1 to the left of the active cell.
VB: Public Sub Button11_Click() Dim wShell, fso, strFldr, xdir As String Dim newbal As String
I am looking for some macro code to create an Input box which will prompt the user to enter a specific date (not todays date so NOW function is not appropriate). This will in turn cause the file to be saved in a specific folder with the date becoming the name of the saved file.
Create an individual pie chart for each row of data (roughly 2000 rows in master list). Data to be charted contains 2 cells (Benefit and Salary). Once the chart is created I need to save the chart as an image (assuming it will need to go into Paint or another program?) Once the images are created and saved to a folder I can add the image link (file location) to a merge document data source. This way I will be able pull in the pie chart (now a picture) with a merge field during a mail merge.
I have created a spreadsheet for my friend. As my friend is a little 'scared' of excel I created drop downlists to complete the spreadsheet i have also protected all the cells that have formula in them so that they can not be deleted.
the spreadsheet he opens is prefect, he then updates this spreadsheet and has done something wrong but doesn't realise, he then saves the spreadsheet, it is at this point he wants a back up copy of the perfect spreadsheet, that way if he goes back into the saved copy and realises that something is wrong he can go to the back up copy and start again. This spreadsheet is filled in once a week, that way if he has done something wrong and has to refer to the back up copy he only loses one weeks worth of work.
I did say that he could choose save as instead of just save and re-name the file but again he is worried that he will forget!
Can we create a back up file automatically? If yes, can some please tell me how to?
I have never used macros or anything so if you can do this, can I ask that you also explain to me how to do it.
I have the following code which saves a certain sheet to a new workbook and then emails it to a range of cells. The directory the file is saved in is from a cell, and changes depending on certain cells in the workbook. I would like to make it so if the folder it is saving to does not exist, it will create it. If this isn't possible with this set up, I would just like it to display an error message if the folder is not present.
VB: Sub Save_Report() Dim wbName As String Sheets("TELELINK & ITINERARY FORM").Copy
In Cell N1 i have 02/02/09 but i have formatted it to look like Feb Invoices 2009. In the strpath below it saves the file to a folder In desktop/"Cell N1" But it tries to save it as c:Documents and SettingsDaveDesktop229 I would like it to save as c:Documents and SettingsDaveDesktopFeb Invoices 2009. Also if the folder does not exist the macro fails. How can change this macro to
1. Change the folder from 020209 to Feb Invoices 2009 2. If folder does not exist then create it.
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
I have been working on the following VBA code, which should copy specific charts from excel into specific places in a word-template.
I Am quite new with VBA, so I googled the code, changed a few things, and it works exactly as it should - except from one thing. When the Word-document is loaded from the template, I would like the document to be "saved as..." Instead of just opening the template. I have tried
I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks
Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String On Error Resume Next strDirname = "Fungicide Quotes"
strFilename = Range("d4:f4").Value strDefpath = "C:My Documents" If IsEmpty(Filename) Then Exit Sub
I have a workbook with roughly 28 tabs(sheets) and i need to be able to run a macro that will create a new sheet for each tab and name it based on the tab name.
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList() 'The code below creates and names a new tab for each members info Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Sheet1") Set rng = Range("Database")
'extracts a list of member or distributor names ws1.Columns("R:R"). AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("T1"), Unique:=True r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r) 'adds the member name to the criteria area above ws1.Range("U2").Value = c.Value ...............
I have a workbook (see attach) That has 2 worksheets. (LOAD DATA AND SKIP) What I would like to do is have VBA that automatically filters for each of the names in column A, creates a new workbook naming the workbook as the filtered name (ex D Fowler) and current date and then copy in all info based off filtered data from columns A thru Y Then save and close for each of the names in column A.
I have a workbook which contains a list of data on the first worksheet. I need for a macro to copy the name in each row,along with its data in that row and paste it into the corresponding locations on a template located on another worksheet. It would then save the file using the date (col. a) , then the words " Scorecard for" and then the name (col. b), then loop for each row until it reaches the end of the list.
I located this code here here.However I dont know how to modify it to fit my needs. I dont have 2 separate categories for two score cards in column b. It would all run off one scorecard and each row of data.
I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."
Dim objXlApp As Object Dim wkb As Workbook Dim wks As Worksheet Set objXlApp = CreateObject("Excel.Application") ' Create a workbook Set wkb = objXlApp.Workbooks.Add ' Delete all worksheets bar the first one. For Each wks In wkb.Worksheets If Not wks.Index = 1 Then wks.Delete
End If Next wks 'Create some worksheets and names With wkb .Worksheets(1).Name = "myWorksheet1" .Worksheets.Add.Name = "myWorksheet2" .Worksheets.Add.Name = "myWorksheet3" .Worksheets.Add.Name = "myWorksheet4" End With...................................
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code: Sub SaveWorksheetsAsPDFs() Dim sFile As String Dim sPath As String Dim fPath As String Dim wks As Worksheet
I have a workbook which consists of 28 worksheets, all of which are protected, and are running on a code which automatically keeps all sheets at "unlocked cells" in the selection field. The sheet is also password protected for write-access…
I would like a Macro, or some sort of code, that when activated… it unprotects ALL sheets (which use same password), creates a folder on the users desktop, and saves a copy of the document in there… entitled "copy of {filename}" It must also remove the "write access" password…
Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
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.
i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format
I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.
As an brief example.
Cell "B3" on the master(worksheet) contains a job name I-40 bridge So initially I would like the Folder name and file name to be I-40 Bridge
However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.
I am currently working with Excel 2007 Pro edition on windows 8. I
I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.
Public Sub SaveAsA1() ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
