I think my code is close but I keep getting an error "Run-time error '1004': Application-defined or object-defined error"
I am trying to rename the active workbook by appending yesterday's date onto the filename, using the Save As.
I might be going about this all wrong anyway. My user needs the macro to save, rename, and close the renamed copy of the workbook but to leave the original workbook open. I'm trying to do it in steps so I might be doing this the hard way. However, my code to Save As is below:
Sub SaveAsRename()
Dim CurrentPath As String
Dim CurrentFileName As String
Dim NewFileName As String
Dim Today As Date
I have the following Macro that I run assigned to a Button.
Sub AskAndDo() If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD") End If End If Application.Quit End Sub.......
By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls" ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.
I got this script from an site and tried it. It runs but I don't see any spread sheets.
I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.
My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV
My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.
How can I set the SAVE AS file name to equal A1's cell contents? (More specifically, when I need to rename an existing open file and place my cursor in A1 and hit Save As, I need to new file name to automatically populate A1's cell contents so I don't have to retype the contents of A1.)
I've written most of the code that I need... the problem is that I don't know how to properly select, move and rename the pdf from excel.
I have a popup to select the file, and I know what I want it to be called, what I need is the code to move and rename a non-excel file from inside a macro.
Here is what I have so far:
Sub AttachPDF()
Dim LastName As String Dim Street As String Dim callDate As String Dim FilePath As String Dim HouseNumber As String Dim FullFileName As String Dim FileName As String Dim Scan As String
Range("D32").Select 'Ask user what file they want to attach. Scan = Application. GetOpenFilename(FileFilter:="Follow Up Scan, *.pdf", Title:="Please Choose A PDF To Attach")
I am using this code for 2nd coping my file and rename it automatically......
[Code] .....
I want when i want another copy always my path should be "C: estabc.xlsm" and For new file in same path "C: estdef.xlsm" an input box ask for the name("def.xlsm") "what new name u want for this new file".
I have some code which will look for a specific file in a given location. Is it possible to use VBA to rename that file without opening/re-saving and killing the specific file?
I am using the following code to rename a file that is being pumped out by another program.
Sub CopyAndDelete() Name "C:Program FilesPATH1234567890_3.tvr" As "C:Program FilesPATHNEWNAME.tvr" ' End Sub
The problem I have is the the file being pumped has a varible name. The nly constant is the _#.tvr. The first 10 digits are random numbers.
I have persued the "rhymn and reason" for the file name with the vendor and have been told it can not be changed and is random. The "_#.tvr" piece comes from the number of reports I have generated for the day. So I have 5 prefined reports that run each day and can recreate the _0.tvr through _4.tvr but the first 10 are random each time.
I would like for the rename to "ignore" the first 10 digits then looking at the file name.
Dim fexport1 As String ' variables for the exported file Dim fexport2 As String Dim wb1 As String 'variables to change between the opened workbooks Dim wb2 As String strTemp = "Please Choose The Exported File" MsgBox strTemp fexport1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls)") If InStr(fexport1, "False") = 0 Then Workbooks.Open fexport1 wb1 = ActiveWorkbook.Name
Else strTemp = "Operation Canceled" End If
The problem is that Crystal Reports generates the file with an invalid worksheet name (it contains a backslash) and I do not have access to modify the Crystal Report. Althought the file can be automatically repaired by Excel when manually opened, the macro chokes and the "Application.DisplayAlerts = False" does not fix it. The only solution I can think of is renaming the worksheet without opening the file.
I am trying to move and rename an excel template file using a macro. My code works fine when it is moving the file locally on my C: drive. However, when I try to do it on a mapped network drive I get a Path/File access error. Here is the relevant code:
[Code] ......
I tried it both ways that are commented out - both give me the error. I have permissions to read/write in all relevant folders. What am I missing here?
I've added an word object to my excel sheet as an icon. The only problem is that it shows the word icon then under it says 'Microsoft Word Document'. This might be an obvious and silly question. But how do I Rename the 'Microsoft Word Document' to a title of my choice?
I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.
FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles() 'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name Dim oldName As String Dim myfile As String Dim newName As String Dim ImagePath As String Dim NewPath As String
Is it possible to have a macro to prompt the user for a tab name, check if it exists and if so copy it and ask the user for the new name for it to be rename to?
From my research on trying to apply this function, I have peiced together several codes and am unable to get my end result. I want to add a worksheet and rename it with text formula on my main "Macro" sheet from cell D1. The name in cell D1 is a date that changes based on a formula. I can get the macro to add the worksheet but then it stops at renaming it:
Sub NewTab() ' ' NewTab Macro ' Macro recorded 9/10/2009 by andrea_1 '
' Dim wsNew As Worksheet
Set wsNew = ThisWorkbook.Worksheets.Add ActiveSheet.Select ActiveSheet.Name = Worksheets(Macro).Range("D1").Value
I run a report each month which renames the tab name called ex. "xxxxx Report 8". The 8 meaning for the month of August. I have created a macro to rename the tab, but each month when i generate the report the tab name change, hence i would have to change my macro again to reflect the tab name. What formula could i use in VBA to change the name of tab so i would not have to change my macro each month?
I currently use as shown below, which does not work since the number changes each month when i generate the report):
