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 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 wrote a simple macro/ function that used to work before I decided to improve it. When I run the macro, I can step F8 until the before last line in the following code
Function bla() As Boolean Set rCell = Cells(1, 1) If rCell.Value <> "" Then x = rCell.Value * 0.02 'Works rCell.Value = rCell.Value - x 'Doesn't work End If bla = False End Function
Excel does stop running the macro, but it doesn't show any error so I don't know what to fix. Steps to reproduce the problem: 1. Create new Excel document 2. Type any number in the very first cell 3. Open VBA, add module and copy above function 4. Type in another cell "=bla()"
I have a Master sheet with data on it that corresponds with multiple sheets in the same workbook that I have links to in the Master sheet. It all works fine until I sort the Master and then my links start sending me to the wrong worksheet.
Is it just the nature of hyperlinks that you can't sort, or is something else going on here?
The sheet was working fine, then all of a sudden (i dont know what i have done) the up and down arrows (on my kayboard) wont work when the sheet is protected, when i unprotect the sheet they work fine, i can use left and right. i have checked the scroll lock isnt pressed/illuminated on my keyboard.
I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])
This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?
I have a useform that loads up with two buttons and I waat to force the user to chose one of the buttons. However, I have noticed that the user can just press the cross in the top right corner of the form to close the window and therefore not press either button.
Is there anyway I can stop the close form button working?
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
The code below works. Now I need that while it does the backup file in the same location, we also took into C: Backup_Contabilidade. Finally also put the date and time.
VB: Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application .CommandBars("Cell").Reset End With
I have a spreadsheet on a server, password protected, and certain columns, rows locked...
This is shared with 6 others and can only be accessed one at a time.
If It so that when the user saves the spreadsheet it saves over itself on the server it's located AND on the individual users computer as backup; how would I go about doing this!?
Whenever I make a change to any of my spreadsheets and click save, a backup is created in the same folder. A sample document is attached. Download it, make a change, then save it.
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 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 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 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.
Option Explicit
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 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.
Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?
I have this formula:
Sub backupbutton() Dim fname fname = "D:" & Format(Now, "dd mmm yy hh mm") & ".xlsm" ThisWorkbook.SaveAs Filename:=fname End Sub
But that just keeps on making multiple copies of the file rather than overwriting the backup in the D: location.
Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.
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 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.