I want to make a macro that will generate a backup copy each time the Excel file is opened. I premade a backup and recorded the following macro, placing it in the sub here:
Code: Private Sub Workbook_Open() ChDir "C:Usersmy.selfDesktop" ActiveWorkbook.SaveAs Filename:= _ "C:Usersmy.selfDesktopDaily Backup.xlsm", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled,_ CreateBackup:=False ActiveWorkbook.Close End Sub
names have been changed to protect the innocent. But now either putting in to repoen the original working excel or manually opening it puts me in an infinite loop of backing up. The probelm seems to be that to make a copy it is making the open workbook the backup. So two part question here:
1. Can I make a copy under the same name automatically when the file is opened while staying in the current one? Example: open the Excel file "Working Copy", which makes a mirror file called "Backup Copy", but stay in "Working Copy".
2. From what I did manage to do, I notice every time it asks if I want to overwrite. I chose "Yes" when recording the macro but this doesn't seem to get captured. can I make it automatically yes without being asked?
Pls see below codes, the codes can record down changes made "from" and "to" and put on the "Log" sheet, however it is unable to capture the original values when I first open the file and make the changes.
Also, how can I put the value of "Now" in column B?
Code: Dim PreviousValue Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value PreviousValue Then
I am wirking on a macro which opens a 'sourcefile' and then do some filtering and vlookuping to distribute the data to several files. When i run the macro from the VBA editor, everything runs fine. But if i run it from .xls file, the macro stops after opening the 'sourcefile'
Workbooks.Open Filename:=sourcefile For i = 0 To 13 'do something... Next
I'm using a macro to open an other excel file to get data from it. this goes by Workbooks.Open Filename:=totalopen (totalopen =Filepath & filename earlier defined) Because the opening file can have the same filename, my question; is it possible to open the latest/newest saved file? So also look at the saving date/time of opening excel file.
I'm working on a macro for work. It's a simple workbook with about 10 tabs. I would like for the user to open up the workbook but it not have the prompt foir enabling or disabling macros. Just on this one sheet, not other macros on other sheets.
I was wondering if there is a way to open up a given program (notepad in my example) ..paste data from cells copied in excel and save the notepad file to a specific location and close it out?? The file path will be given from a data cell in excel as well as the file name.
From searching the forums i've come up with the way to open notepad using the lines
Dim RetVal As Variant RetVal = Shell("C:WINDOWSSYSTEM32 otepad.exe")
I have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!
here is my code
stPath = ThisWorkbook.Path stFilename = "Retention" & "" & TextBox1 & ".xls" stFullname = stPath & "" & stFilename 'completes full path of for TextBox1
On Error Goto Errorhandler: 'go to this if file does not exist Workbooks.Open stFullname ActiveWorkbook.RunAutoMacros xlAutoOpen
I would like to have a cell that gives the date of the last save made on the file itself. Like, it has been saved on Oct-3rd for te last time, I open it on Oct-10th and see thru that cell that all last changes were made on Oct-3rd.
I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path: [url]
So the command is this: Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
I am using this code to create a backup copy of my workbook:
Code: Sub DOUGHMON()Dim fname fname = "C:UsersAndrewDesktopWEEKLY SALES REPORTS" & Format(Now, "dd mmm yy") & ".xlsm" ThisWorkbook.SaveAs Filename:=fname End Sub
The only problem with this code is that my current file gets closed and the backup is open. I'm not sure if that is exactly what happens, but when it is done the current file that is open is the copy, and I would like the original file to stay open.
I have this macro currently running on an original excel document
Private Sub Workbook_Open()Dim fname fname = "C:ackupEconomics Tracker - " & Format(Now, "dd mmm yy hh mm AM/PM") & ".xlsm" ThisWorkbook.SaveCopyAs Filename:=fname Sheets("Menu").Activate End Sub
Which creates a backup of the document each time the document is opened; I was wondering, is it possible to remove that particular macro from the backup as opposed to saving the backup as .xlsx? I don't want to be able to open a backup and the backup makes a backup
That particular macro is running on the main workbook.
I have a worksheet with 35,136 rows of data. I want to calculate the average of every 4th cell in the same column. Is there a way to create a macro to select every 4th cell in the average formula? If so, can I then substitute min and max for average?
Can Active-X control button macro be placed in a regular module, or does it always have to reside in the sheet module? Or is the only way to accomplish this is to have the macro in the regular module being called from the button macro in the sheet module?
I somehow seem to remember a way to move/assign an Active-X ControlButton macro to a regular module, but maybe I'm wrong?
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?