I'd like to add a "save as" function into an existing macro that would automatically save my worksheet in a network folder using a filename and today's date (no slashes). I have read several other posts with a similar Q, but mostly what I am finding is answers with a very "situation specific" copy/paste-able macro.
So here is my situation:
Filename: FILENAME MMDDYYYY
Folder Location - Let's call it: \companyFile2009
I want a macro that will save a copy of the current file using specific naming convention. My file is called OTR2CMMASTER - I want the macro to save a copy as OTR2CMSAGE(CURRENTMONTH/YEAR). The OTR2 is a named field (ProjectCode) in the workbook.
View 5 Replies View RelatedIn a workbook, I use a macro to extract a sheet and save it as xls.
What code should I use to prompt the user to define the location and name of the new file to be saved?
I'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.
What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
I have a problem with the following code
View 2 Replies View RelatedBelow is a bit of a big macro i'm writing but I'm not sure how to edit my code to append the date after the filename. Then i'll need to refer to the exact filename later in the code?
View 2 Replies View RelatedI'd like to record a macro that allows me to take the current file "file.csv" and save it as "file.xlsx"
I can't get the variable right (just messing around in VBA to have it get the file name and save it as a different format (csv to xlsx)
I've tried:
Function GetFullName() As String GetFullName = ThisWorkbook.FullName End Function
I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.
Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?
Is there a way to save a file and have it automatically put today's date in the file name?
Example: original File name = test.xls
desired file name = test072807.xls, or test.072807.xls, or test.07 28 07.xls
So, I open the file, do whatever, and then click save.
When I click save, it does one of the above, given that today is 7 28 07.
Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.
View 9 Replies View RelatedI'm trying to use the code below to paste special my first sheet to remove the formulas, then delete all other worksheets then save as a reference number (which is linked to the worksheet im saving) but I also need it to save in a specific folder.
Sub SaveAsCell()
ActiveWorkbook.Save
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Cat Nos").Visible = True
Sheets("Supplier Info").Visible = True
Sheets("Codes").Visible = True
Sheets("Buyers").Visible = True.................................
way for my engineers to save a field ticket with a certain name based on data from a couple of cells in the worksheet. Re: Auto generate "Save As" filename from text and tried to use some code posted in the thread, but I an still not having any luck.
What I want to do is create an active X button when clicked on, would save the workbook to a certain folder. I want the name to look like this:
SO1!M3_SO1!M6_SO1!H2.xls
This is what I have so far:
Private Sub SaveMe()
ThisWorkbook.SaveAs Filename:="C:usersdefaultdesktop" & Range("SO1!M3").Value & Format(Range("SO1!M3").Value, "text") & ".xls"
End Sub
Would I click "general" or "workbook" in VBA when I enter this code?
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 RelatedOur business has a spreadsheet set up for invoicing. A Macro has been created to increase the invoice number, save to the desktop as a PDF with a file name taken from the cells
This is working on my colleagues mac but when we've loaded onto my computer it errors, when I debug I am getting the following error:
Sub Macro3()
'
' Macro3 Macro
'
'
Range("g10").Select
[g10] = [g10] + 1
[Code] .......
I have a macro that currently saves and prints the sheet I'm working on. At the moment, it saves everything in the same place and always calls the file the same name.
In the worksheet, a lookup does actually specify what folder it should be saved in and what name it should be given. How can I write the code so that it pulls up the name and the folder directly from the sheet it's saving?
So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.
Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.
I have a Workbook with around 10 worksheets on, each worksheet is a form.
And when one form is completed we have to save a backup. So i want to assign a macro to a button on a worksheet so when clicked it will save a copy of that worksheet ONLY to a destination and not all the other worksheets along with it.
The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"
Sub RENAME2()
Dim strWbKill As String
Dim myName As String
myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in
myName = myName & Application.Cells(3, 7) & "_" 'cell G3 Style
[Code]...
I need a macro that I will be using for a workbook that has many workseets. I need to save a worksheet "Test1" and save to a path found on worksheet "Test2" cell A1 and make the file name the value of "Test2" cell A2.
View 9 Replies View RelatedI've been using this macro to pull data from multiple files, but instead of it creating a new tab, how can I have the macro pull the data and save it on a specific tab name(ex. Comp Stores). Here's the code I have currently.
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
Sub Combine_Workbooks_Select_Files()
Dim MyPath As String
[code]....
I have code to save an excel sheet as a PDF and attach it to an email but when it try to modify it to save as an excel, it causes errors. Below is (1) the excel code I'm trying to fix; and (2) the working code for my PDF function. When I run the macro to save the excel worksheet in a new workbook, it creates the sheet in the a separate workbooks, but then gives as Run-time error '9': Subscript out of range." When I run debug, the line in red is what's highlighted. So it's not liking something with the filename/path??(1)
Code to save worksheet into new workbook (broken)Sub SaveAsExcel2()
Dim FileFormatstr As String
Dim Fname As Variant
[Code]....
I have a multi-worksheet spreadsheet in excel 2003. I want to produce a macro that will just save a copy of the front worksheet values only. That is I don't want to save any formulas or any of the hidden worksheets, just the front page report, plus it's formatting/layout.
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.
The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).
have seen macros that create a seperate sheet & save by tab number etc, & wonder if its possible to save each sheet in a workbook as the value defined in each sheets cell A2. is there a vb macro available to do that ? if so where ?
View 9 Replies View Relatedhow to extract the current/active worksheet's file's name using VBA code. Let's say I have a an excel file with the name "Test.xls" Which function do I use to return Test (with or without the extension)?
View 4 Replies View Relatedam 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 a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:
' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.
Filename = "C:Folder1SubFolder1" & ActiveSheet.Range("InvNbr").Value & ".pdf"
' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.
SendKeys Filename & "{ENTER}", False
' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here....
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies View Related