I have a workbook that I constantly am improving with new macros and VB routines. I sometimes have two versions available for use at the same time. I would like to create a macro or routine that will tell me if the workbook has been saved. A simple cell color change to indicate I've saved would be fine. Also, if I DO save the file and the cell color changes to indicate I have saved, I would like the cell color to change back if any change is made to the workbook. This way I will always know if I need to save or not to keep latest changes.
I have created a template with a form. Now I'm using (the code is in ThisWorkbook)
Sub Workbook_Open() frmMyForm.show End Sub
This runs the form also when opening the template or a saved workbook based on the template. I want the form to run only when creating a new workbook based on the template. How do I do?
I have a macro in a workbook template,that when the workbook is open it populates a cell with the date and time such as 111711.507. This becomes the contract number for this workbook.. They complete the form or workbook and save it. The next time they open the saved workbook the macro runs and changes the contract number to a new number. I want to disable the macro on the saved workbook from changing the number.
I use the following macro to save my worksheet. However, if I click cancel when the save box is open, then I am presented with an error. How do I get it to skip the error and show a message box instead that says "File not Saved!".
how I can have the latest date a file was saved showing on the worksheet? For example, I open a worksheet that was last saved on Sept 22nd, and after updating it today, I save it and want the date to show today's date?
I'm pretty sure I've seen it in some files in my working life, so would appreciate advice on this!
Need VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.
Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.
I am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?
Here is name code:
[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that
If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...
I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.
So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed
I currently have the code below that closes all but 1 sheet in my workbook called Main
Is there anyway i can clear a range of cells on a couple of sheets within the workbook when save is pressed?
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For a = 1 To Sheets.Count If Sheets(a).Name "Main" Then Sheets(a).Visible = 2 Next a End Sub
I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate() If application.worksheet.lastsaved = msolastweek Then range("RANGE").clearcontents End If End Sub
I've designed a performance indicator (PI) recorder for up to 12 users, it has a userform allowing the users to input their data and a button to save the data and unload the user form. the data is saved using the following
Dim NextRow As Integer On Error Resume Next 'skips line if workbook is not open ActiveWorkbook. Sheets("DATA").Activate 'sets the "data" worksheets as active NextRow = Worksheets("DATA"). Range("A" & Rows.Count).End(xlUp).Row + 1 ' finds the next empty row With Worksheets("DATA").Range("A" & NextRow) 'inserts the data from the user form there follows a series of offsets to slot the data into the correct column, then this code to save the workbook ActiveWorkbook.Sheets("reports").Activate Application.DisplayAlerts = False ' suppress overwrite warning message ActiveWorkbook.SaveAs "MY PATH" CreateBackup=False Application.DisplayAlerts = True MsgBox "Spread sheet Saved"
The problem I'm having is that when the user then closes the excel application there is a conflict between the users 'saves' i.e. one ultimately will overwrite the other. is there a way round this ?? (this is in EXCEL 2000)
I have working code to unhide specific worksheets from a large number of hidden worksheets and would like to be able to delete all the hidden sheets when the workbook is saved or maybe have a button to delete all the hidden (un-needed) worksheets beside the selection drop-down menu, so that only the selected sheets are saved.
I have one workbook named "Data Master" with numerous worksheets... all sheets are named with the following 6 digit format, eg. 005001, 005024, 005133, 005456, 005763, etc.
I have another directory with files each beginning with the particular 6 digit cost centre number.
I was hoping to have a macro that will... for each worksheet in "Data Master" check a specified directory containing the cost centre files, for the file beginning with the relevant worksheet's 6 digit cost centre number... and simply paste a copy of the worksheet in "Data Master" to the end of the existing sheets in the cost centre file.
When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click() Application. ScreenUpdating = False Dim LCSfile As String LCSfile = frmSelectFile.Listbox1.Value On Error Goto ErrHandler Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV" ErrHandler: MsgBox ("File is not quantitated. Please select another file.") Application.ScreenUpdating = True
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
Macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date.
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 have written a macro but unable to make it work. The macro works but not the way I would like it to.
The macro has to save an excel sheet to a separate folder as a pdf format, a message box should pop up confirming the pdf file and then attach the same saved pdf file to an email.
Now the macro saves the pdf file where I need it to be saved, the msg box pop up and then the pdf file opens up on top of the msg box. If I close the pdf file the msg box which was hidden under the file is now shown. When I click "OK" it goes straight to outlook with the email addresses attached but the saved pdf file is not attached.
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code: Sub Test() Dim Rng As Range Dim wb As Workbook Set Rng = Range("A1:A2") Set wb = Workbooks.Add With wb Rng.Copy
I'm working on a project where I need to import 150 csv files into an Excel2010 workbook, with each csv file being on a seperate worksheet.
I would like to set up a macro to point to the folder I will have these saved in this up so that each month when I get updated csv files I can repeat the process and bring in the new data. how to create this, I'm very new to VBA?
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I have 10 Workbooks open, one primary workbook where the macro resides, and 9 other, randomly named files (workbooks). The macro needs to copy the values from Workbook2,Sheet1, Range("A1") and paste them into the primary file (location irrelevant for now), close Workbook2, then do the same thing for the remaining open workbooks. Is there a "next" workbook function that doesn't need the "name" of the next workbook?
