I am trying to manipulate some worksheets during a Workbook_BeforeClose event. On sheet names that end with "sd" they need to alphabetize the data and autofit the columns. All sheets should hide on close, except "order". This is what I have but warning - it did something bad to my workbook. I can't get it back again. Had to force close Excel.
Is there a way / code i can put into a macro which can autofit a group of sheets or a whole work book. I have a workbook with 100's of sheets and it takes alot of time to click each sheet and autofit them manually.
I do run a macro for each sheet, so if i could build it into a macro by adding the code to it that would be great
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
I am attaching a file with an example of a spreadsheet that I am trying to sort out. In this example I have 3 samples (I could have many more). Each sample has 8 columns associated with it (N, M, I, F, S, MS, KM and KD). The length of the dataset is different for each sample. The MS column is the same as M but contains a few zeroes. What I am trying to do is:
1) generate one column (MSA) containing only unique values (no zeroes) from columns MS1, MS2 and MS3. The unique values should be selected within a specified tolerance (for example, 0.001, which makes 52.00706 from MS1 and 52.00701 from MS2 duplicate values although they are not exactly the same)
2) generate 3 columns ( named SS1, SS2 and SS3) with sorted columns S1, S2, and S3 so that for each value of MS in column MSA each of the three columns will list the corresponding value of S1, S2 and S3 (zero if there is no corresponding value)
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..
I have the below code in a macro attached to a button. Basically, if i choose yes to the message box at the beginning then the macro copies the all the cells on the sheet 'selections', opens up a new workbook, pastes into here, deletes some unused cells, rows and columns, then sends the sheet as an attachment on an e-mail.
What i also need to do is when i have sent the e-mail, the macro closes the new workbook without saving changes.
I have a workbook that contains 4 macros. This workbook is copied by vb.net application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.
1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?
2) Is it possible to close the original workbook while executing the macro from the copied workbook?
3) Will that affect the macro in the copied workbook?
4) If possible to close the original workbook, kindly help me with the code.
I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.
here is the code I have on open that works fine: -
Private Sub Workbook_Open() If Environ("username") = "Bob" Then Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = False Else Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True End If End Sub
I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!
Here it is: -
Private Sub Workbook_BeforeClose() If Environ("username") = "Bob" Then Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True ThisWorkbook.Save Else Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True End If End Sub
So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)
One of my computers pulls info from my cash register once everyday (sales info). Then I have a excel monthly sales sheet that pulls the info from the register info and puts it in the right places. So the computer pulls the register info at 9:20pm everyday, then I have my task manager open excel at 9:25 everyday and it has a workbook open macro to automatically pull the info into the excel spreadsheet. Here's my problem-- I need to close it.
I'm sure I could use a workbook close macro, but when it tries to close it won't it come up with a save, don't save, cancel prompt? Nobody will be there to hit SAVE so it won't close? Is there just a macro that could save it then close it?
I have an Excel workbook that runs some code in the back. When I close the workbook file it seems to close OK. However if I then open the VBA editor window I still see the file in the project explorer. If I re-open and then close I have 2 instances in the project explorer... and so on. I have tried setting all objects to nothing etc. but the problem still manifests itself.
I didn't receive a response to a previous post where I was trying to use a batch file to close and open a workbook at a scheduled time everyday. I was thinking about this problem over the weekend, and thought that it would probably be easier to just use a macro to close the file at a certain time everyday (if it is open) and then use a batch file to open the file a couple of minutes later.
I'm having problems with what I thought should be pretty simple though. I tried this to close the file at 5:15pm
I am hoping someone can help. I am new to writing macros and have the following in the this workbook page. When opening the macro does exactly what I want and expect, but does not do the "before close" part. If I select the macro in editor and run it, it does just what I want, so dont understand what is going wrong. Do I have to put in some kind of prompt, or manually run this before close - preferably I would like it to just run.
secondly, I have password protected the workbook, but have to put the password in the macro, is there any way of making this invisible to anyone who then reads the macro....
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?