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?
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 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 call the macro below from a Workbook_BeforeClose sub; however, if the user has not saved changes before they hit the close then the Sub SaveFile runs and then after the backup save they are then asked do you want to save changes.
I initially had this sub run with Workbook_BeforeSave, but I don't really want to run this everytime the user selects save.
Any ideas on how to how the save changes first and then the backup?
I know how to create a backup file for an excel file (General Options Menu in Save As dialog box, we have to tick the "Always create backup" check box). When we tick this option the backup file is created in the same folder. Can we specify the path were this backup file can be stored and updated each time when we save the main file.
I have trouble with the last part of a backup script, this part checks in subdir and should delete the oldest file (of 4) but since a while back it instead delete the new file saved earlier in the script and I dont know how to fix it. Here is the ...
I was able to find the coding that I needed for being able to have a backup of my file. So when I save a workbook and close I also get a backup copy in another folder that is dated. But I am having problems getting the code to work. Probably because I do not understand it correctly. Here it is.
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.SaveCopyAs "C:Documents and SettingsTeacherDesktopTEST" & _ Format( Date, "yyyy-mm-dd") & " - " & ThisWorkbook. Name End Sub
Where it has, ThisWorkbook.Name, do I put the name of the workbook? Not sure.
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
I have a database (attached) that has data entered into the "Progression" sheet. This data is continually updated, and once each row has a section date, I have a macro that moves the data from "Progression" to the "Complete" sheet (and from there, into a specific month sheet). I AM REALLY CONCERNED with data accidentally getting deleted (especially when/after getting moved to "Complete"). I know that you can link cell values from one worksheet to another, but since my data is continually changing in the Progression and Complete sheets, the linkage wouldn't maintain the correct cell values from day to day. Is there a better approach to creating "backup" data in excel than this linkage option? Preferably something that runs in the background without my input?
An option could be that I move my data from Progression into both the Complete sheet and then a hidden "Master" sheet when I run the macro. If I chose that route, how would I modify my coding to include transferring to the Master sheet as well?
Lastly, a simple linkage between the Master sheet and another sheet in a second workbook should be sufficient as a backup method?
I need to save an excel workbook when a "backup" button is pressed to a folder named "Backups". I want the name to be the "date" (09/07/2013) only and I want it to automatically overwrite anything previously saved for that day.
I have a workbook which i wanted to make backup, i have found several codes and from that i have modified according to my requirement, now everything working fine without any issues but i don't know how to protect the backed up file with write password.
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons. Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later): Template file name: template_order.xlsm Template file location: \servershared emplate
Total list file name: total_list.xlsx Total list file location: \servershared otal
The template file exists of a "general order info area" A1:N10 The column heading for order data is located at A11:N11 The selection to be copied is A12:N550 - But only rows where column A includes data (not empty). (If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?