have had to try to automate a function which will transfer all rows of data that an operator "checks" in a check box, to a seperate sheet before printing. Written under pressure with boss at my elbow so I will tidy later.
My problem is that there is a command within the macro (after the filtered data is copied over to Priority sheet) which will not allow the macro to fully execute when the file is shared.
Copy file attached.
I have a shared worksheet my self and a few others key pricing into weekly, more or less a price check for stores we service. I have created another workbook that has the same basic template with links to the "shared" worksheet. In this workbook Im trying to run a macro to copy the pricing "template" into a new tab in the workbook. I keep getting this error as it pertains to "sheet". I would like the tab that is created to be named whatever the current day is if possible.
Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Select
Range("A1:AM61").Select
Selection.Copy
Sheets("Template-Link").Select
Sheets.Add
Sheets("sheet").Select
The shared worksheet resides on a secure network drive, so I can access it from anywhere. For years, I've had my secretary cut and paste out separate reports on individual salesman booking/commission performance. I thought taking advantage of Excel's native Pivot Table features, would save an awful lot of work, and probably lessen the chance of errors. Then I discovered that Pivot tables don't work with shared workbooks.
So I tried un-sharing the file. I discovered that the Pivot tables worked fine, but that I had to
re-create them ( seven sales guys, 4 independent geographical territories) each reporting period. The Pivot tables weren't dynamically updated each time additional orders were added to the main list (entry worksheet). I was very careful in laying out a new version of the "entry worksheet", and eliminating any unecessary column and rows. My immediate thought is that for the Pivot tables to work dynamically, I have to have dynamic ranges in the entry worksheet.
I've added some code the adds a button to a worksheet
Public Sub AddSheetPrintButton()
Dim btn As Button
Dim t As Range
Set t = ActiveSheet.Range(Cells(1, 6), Cells(1, 7)) ' button position
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "sheetPrint"
.Caption = "Print Sheet"
.Name = "Print"
.Font.ColorIndex = 10
End With
End Sub
This works fine until the workbook is shared!
I'd like to grant access to selective columns on a shared worksheet.
For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else
The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!
Is there any way around this or perhaps a completely different way of approaching it?
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.
I've set up a macro to run in the workbook open event, to open a user form that restricts user access to the spreadsheet. This works fine, but I need to make the spreadsheet shared and when I open it the macro doesn't run. it's still possible to use the workbook_open event on shared workbooks or if not suggest an alternative method of running the macro on opening.
View 2 Replies View RelatedI 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?
Ive got a problem with a work book im working on at the moment, my company has various different documents created in excel, like a price list, cost price list & stock levels (all Independant) I have had the idea to join them all into a single shared workbook using an idea I found and modified over a year ago on this site, that had the promise of allowing multiple users to see the nessesary parts of the same document. the advantage of this being any new parts or prices or suppliers added would update all users at once. So ive got this workbook that when you open you get asked for a username & password this then hides/unhides, protects or unprotects to suit the users needs. This works great when unshared but when I share it the login box wont display
The Administrator username is jamie and the password is joshua
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 View RelatedAny way to bypass the unprotect sheet in a shared work book, this is what i came up with so far
[Code] .....
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................
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?
I have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.
So I suppose the query is three fold.
1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:
"C:Documents and SettingsO033116Application DataMicrosoftAddIns"
The "O033116" is where I will need a search completed to obtain the user id.
The addin is called ServiceCredt.xla
3, Activate the installed addin.
The reason I want it this way is so the user is unable to access any of the code and we are in control.
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Dropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.
View 5 Replies View RelatedI am designing a workbook that techs in my lab can use from any of our network computers to submit requests for making chemical solutions they need to run their tests. Currently they have to fill out a paper form to make the request, so this should make things a little more efficient and less paper intensive.
I have the workbook designed and working, but now need to implement it as a shared file and this is where the problems are occurring. I assumed that shared files could allow access to the same file AT THE SAME TIME with no issues, but that is not the case in Excel97 on our network. Using two networked computers side by side I have tried updating the shared file at the same time and got a myriad of popups. The include:
1) Do you want to save the changes you made to "..."? (I did not include the actual filename here.)
2) A file named "......" already exists in this location. Do you want to replace it?
3) "......" is now available for editing. Choose Read-Write to open it for editing.
4) This file is locked. Try the command later.
5) This file has been locked by for saving. Try again later.
Question:
Are these error messages normal for shared files? Do some reflect issues more with the network than with Excel? Is there any way to get VBA to simulate clicking a Yes, No, or Cancel, or OK whenever a one of these specific messages comes up?
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 RelatedI have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.
View 4 Replies View RelatedI have a 4 MB Excel file containing some very basic VBA that is shared on a server (it is a large file because it contains LOTS of "VLOOKUP" functions). But the VBA only works a part of the time. I assume the reason it might not work is because the server at that time was under high traffic and thus the VBA failures can be traced to poor server performance at that time.
View 4 Replies View RelatedWe have Excel 2003 at our workplace. I have shared a file, which is used by many users, so we can use the file at the same time. My problem is that if let's say I am writing a value in cell C2 and another user uses the same cell and writes another value there, and he saves the file and then I try to save the file as well, Excel will ask me which value to keep (the value written by the other user, or my value). Actually what I would need Excel to do is to save both, and put one under the other.
View 2 Replies View RelatedI 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 RelatedPlease follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.
View 14 Replies View Relatedhow to speed up a shared excel worksheet?
View 4 Replies View RelatedI have a master timesheet that i would like to update for several employees. Each employee will be sending their own spreadsheet at the end of the month. the columns in the spreadsheets have the date, the employee's name, the project they worked on, and how many hours they worked on that project.
when i receive all of the employee's spreadsheets (they will be emailing them to me) then i would like to import the data into a single table that i can then evaluate using pivot tables and pivot charts.
I tried using sharing and merging but i can't figure out how to have their information merge into a single table without overwriting each other (the numbers of rows with information is different for each employee and is not predictable - it will vary from month to month).
I would like to make this as automatic as possible. Ideally, they will send their spreadsheets to me and i would like to have their information automatically imported into one big table.
how to add ticker( splash roller message to shared file) like when the user opens any sheet in shared file there should be some rolling msg in the bottom.
View 9 Replies View RelatedHow 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
I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.
View 4 Replies View Related