Auto Run Macro Once Daily On Shared Workbook
Sep 13, 2007
I have a shared workbook where 5-6 people could be updating the log sheet at any one time. The problem is a I have a macro that I would like to run to update ( cut n paste to different sheets, etc) that doesnt like running when the workbook is shared. What I currently do is have a button that when clicked - changes the document to exclusive, runs the macro, then changes back to shared. I was hoping I could run the macro on an worksheet event? But i'd like it to run only once - Possibly when its first opened for the day by anyone of the users.
View 4 Replies
ADVERTISEMENT
Jun 7, 2013
We are currently utilizing a shared workbook (yes, I've read the inherent problems with this). This workbook needs to automatically save any changes and refresh itself every minute. We are seeking an answer to this goal, perhaps utilizing VBA code ?
We have researched the forums and attempted numerous snippets of VBA code and different settings already available in Excel (ver 2010) without success. And, we have tried using the Advanced Settings (Shared Workbook / Advanced Settings (5 mins)) .... however the display of newly added data is not updated on all viewing terminals unless we close the shared workbook on those viewing terminals and then re-open.
View 14 Replies
View Related
May 11, 2009
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
View 5 Replies
View Related
Sep 3, 2012
This auto run macro does not seems to run/repeat itself daily. I can't figure out why am I missing something? My end result should be that these reports run at the times listed on the macro every day 24/7. The spreadsheet will be on a computer that is never off.
VB:
Private Sub Workbook_Open()
Application.OnTime TimeValue("8:00:00"), "AThirdshift"
Application.OnTime TimeValue("8:00:00"), "BThirdshift"
Application.OnTime TimeValue("8:00:00"), "CThirdshift"
[Code] .......
View 1 Replies
View Related
Mar 20, 2014
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
View 4 Replies
View Related
Sep 25, 2007
I have a workbook which I need to give shared access.
The workbook has macros which make sheets visible and VeryHidden and also give certain sheets protection. Also a filter runs on one sheet.
When I give the workbook shared access, the macros error?
how I can give me workbook shared access but still allow the macros to hide/unhide sheets, filter and protect worksheets?
View 4 Replies
View Related
Aug 18, 2014
Any way to bypass the unprotect sheet in a shared work book, this is what i came up with so far
[Code] .....
View 4 Replies
View Related
Aug 30, 2006
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes".
Sub Newsheet()
sheetname = Format(Now, "dd-mmm-yyyy")
MsgBox sheetname
sheet_count = Worksheets.Count
'Checking for Replication................
View 9 Replies
View Related
May 28, 2008
I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url]
Option Explicit
Sub ExcelDiet()
Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?
View 6 Replies
View Related
Jun 2, 2014
I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.
The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?
View 3 Replies
View Related
May 6, 2006
I have an Excel workbook which is networked throughout our business. Its been in daily use for years with no problems. Every month I add a new sheet and to do that I untick the "allow changes by more than one user...etc". However this month the tick is greyed out and wont let me clear it. The pc's run windows XP professional with Excel 2003.
View 2 Replies
View Related
Feb 15, 2014
How to get an automatic macro to run from my first sheet (sheet 1 of 10).
However, on my first page, users must enter 36 numbers in exact order and then those numbers need to automatically be copied to start at A3 on my following 9 sheets.
View 3 Replies
View Related
Jul 22, 2009
I have a workbook that is no longer being shared. I tried clicking on Tools > Shared Workbook. BUT i keep getting a message saying "The file C:CCTPAccountsSummary1.XLS cannot be found". Is there any way to kill this Shared workbook so I am free to modify everything?
View 2 Replies
View Related
Dec 16, 2008
Please follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.
View 14 Replies
View Related
Aug 19, 2013
how to speed up a shared excel worksheet?
View 4 Replies
View Related
Mar 5, 2010
How do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below
HTML Sub Time()
Call Save
Application.OnTime Now + TimeValue("00:00:45"), "Time"
End Sub
HTML Sub Save()
ActiveWorkbook.Save
End Sub
View 9 Replies
View Related
Nov 16, 2006
I have a workbook that i am trying to make a shared workbook.
Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.
Once a workbook is shared it's protection cannot be changed.
I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).
Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.
Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.
View 6 Replies
View Related
Jan 9, 2007
We have a worksheet, which contains charts and data which requires updating by different department.
However, when I have this work- book as a Shared workbook (Tools - share workbook), The charting and macro has all been deactivated.
Would anyone have any idea on how do I share a workbook - while being able to also update the Excel Charts within it ?
(Different user will be updating different charts, and there will be no conflict in between them)
View 3 Replies
View Related
Aug 30, 2012
HTML Code:
Private Sub SaveAsAndShare()
ActiveWorkbook.SaveAs Filename:= _
"S:LdnPath1Path2Path32012Folder1Folder 2Aug12ReportName " & Format(Date, "MM-DD") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub
HTML Code:
ActiveWorkbook.SaveAs FileName, , , , , , xlShared
My code is the first bit, the 2nd code I found from another post, I want to add xlshared to my code above so it is shared when saved down.
View 4 Replies
View Related
May 21, 2009
Which version of excel allows you to do a Track Changes without switching to a shared workbook? I am awared the Excel 2002 version allows Track Changes on the conditioned that the workbook is switch to shared mode.
View 3 Replies
View Related
Oct 6, 2009
1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
View 10 Replies
View Related
Nov 8, 2011
Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
View 2 Replies
View Related
Aug 18, 2009
Our main spreadsheet in the office is a shared filed which is accessed by 3 pc's.
We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
View 9 Replies
View Related
Sep 8, 2006
i have a workbook with picture and that workbook in share mode.
i have to move picture in share mode.
View 4 Replies
View Related
Oct 27, 2006
I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
it is making a well laid out and good sheet look like something a child has done
I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
View 5 Replies
View Related
Jan 17, 2007
My macros don't work when the workbook is shared. They are not stored in my personal workbook and so are available to all users. What's going on?
View 3 Replies
View Related
Feb 28, 2007
Using Excel 2000, I have a shared workbook which is effectively a single sheet, flat database. Rows contain dates, and when certain dates occur, I want to send an email alert to specific users. I have searched and found lots of guidance on that aspect, but am wondering how best to trigger the email alert. At least one, and up to 10 users access the shared workbook at different times of day, and I only want to review the data and send the email alerts to the relevant users once per day.
I thought about a timed event, but cannot be sure the shared workbook will be open. I wondered whether an event in Personal.xls might do it, if I ensure I open Excel every day, but it's not ideal. Finally, I wondered if I could somehow trigger the alerts when the first user opens the workbook - but can't find how to capture that.
View 2 Replies
View Related
Jul 24, 2007
Can you use or apply advanced filters in a shared workbook?
I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).
View 4 Replies
View Related
Sep 8, 2007
SETUP: I have a shared workbook that is accessed by many people. It is designed to control the flow of work during the monthly close process. These processes are controlled by macros. There are 40 sheets in this workbook.
PROBLEM: On of my location's workbooks frequently loses half of it's sheets in the workbook. Counted in the neighborhood of 14 to 19 sheets go missing.
EFFORTS: I have attempted to Delete, Cut, Move, etc... these sheets with no success.
Only when I put the file in an exclusive state can I manipulate the sheets.
View 7 Replies
View Related
Apr 6, 2008
Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -
A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box
B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.
Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.
Function IsFileOpened(StrFilePath As String) As Integer
Dim FileNum As Integer
'First check filepath exists
If Len(Dir(StrFilePath)) > 0 Then
FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
If Err.Number <> 0 Then
IsFileOpened = 1 'File open
Else
IsFileOpened = 0 'File Closed
End If
Close FileNum
Else
IsFileOpened = 2 'File not found
End If
End Function
View 2 Replies
View Related
