Lock Sheet But Not Pivot On Sheet?
Jan 25, 2012
I have a sheet. At the top I have a pivot that essentially lists all our Depts. Below is all kinds of data and calculations dervided via: vlookups on the dept chosen above. The sheet rerfreshes itself each time a new dept is chosen.
Well, they wanted certain cells protected. I did the ole: Tools then: Protection: then: Allow Users to Edt ranges and added the ranges that should be left unprotected after protection is applied. Of course the pivot was left unprotected.
Once you do the: Protect Sheet and and try to click the pivot dropdown to select a dept...it remains locked!!! An error pops up and says you can not have a pivot open on a protected sheet. WHY!! The range is open!!!
I need the user to be able to select their Dept from a dropdown and then my vlookups work off that.
View 2 Replies
ADVERTISEMENT
Jan 30, 2014
Excel 2007.
I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.
Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.
Monday
Dig
Drive
Dave
4
5
Bill
2
7
Tuesday
Dig
Drive
Dave
2
7
Bill
8
1
I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).
Dig
Drive
Dave
6
12
Bill
10
8
So far, lovely.
But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.
So if I clicked on 10 above...
Row
Column
Value
Bill
Dig
2
Bill
Dig
8
That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.
So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?
View 1 Replies
View Related
Jul 6, 2012
In a sheet (4) I have a pivot refreshed by a VBA macro code. Since I have defined a Chart on this pivot, I need to copy this pivot in a new sheet to avoid to reduce rows using a filter of the Chart. I copy this pivot using this VBA code
Sheet4.Select
Sheet4.Copy After:=Sheet2
I have e new sheet with a number Sheet(n) not equal to maximum sheet number +1
For a new run of the VBA macro I need to delete this sheet
Sheets(n).Select where (n) is not = maximum sheet number +1
ActiveWindow.SelectedSheets.Delete
Then I need to avoid the message box where I need to confirm to delete the sheet because the sheet is not empty.
Is there a solution to copy a pivot in a new sheet replacing the previous pivot and vithout changing the number of the sheet?
View 1 Replies
View Related
May 14, 2009
how to lock a sheet in Excel 2007 so that no one can navigate to that sheet.
View 9 Replies
View Related
Oct 13, 2008
I need to get rid of the option to DELETE a sheet. However, when I password protect the WORKBOOK (which does this fine) none of the Macro buttons will execute, even with Macro security settings set to ENABLE ALL.
Looks like I will need some VBA code to not allow any of the sheets in the workbook to be deleted.
View 5 Replies
View Related
Nov 27, 2011
I have Book having 11 Tables.
In each Tables in Column F i have formula like., =INDEX(Sheet2!A:A,MATCH(Jyotindra!G10,Sheet2!B:B,0))
I want to lock only column F for all Tables. and hide formula
All other columns and cell should be free to excess or edit.
How can i do that for all sheet at once?
View 2 Replies
View Related
Oct 18, 2009
Some of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.
What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.
How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.
View 10 Replies
View Related
Jan 16, 2014
I am trying to Protect a Sheet to lock the width of just one column. Does the lock apply to all the columns in the worksheet or can it apply to a single column only. I can't seem to get this to lock just one column-if it is possible.
View 1 Replies
View Related
Feb 23, 2007
I've got a work book with numourous password protected sheets which i quite often have to update (as i use around 10 of these with at least 10 sheets in each its time consuming). I've had several goes at creating 2 macros to unlock and then lock the sheets (with my password). I've managed to create on that unlocks the sheets using the password but i can't get a macro to lock the sheets up again using the password.
View 9 Replies
View Related
Dec 13, 2007
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
View 14 Replies
View Related
Jul 18, 2007
I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red. Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes. I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet. Here is an example of the worksheet. The range of cells I want to lock are the ones in bold.
View 3 Replies
View Related
Dec 24, 2013
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
View 4 Replies
View Related
Oct 22, 2007
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
View 11 Replies
View Related
Apr 18, 2009
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
View 8 Replies
View Related
Jan 12, 2010
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?
Also rename the new sheet (February) cell B3 the same as new sheet's name (February)
So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
View 9 Replies
View Related
Mar 17, 2014
I have two separate data tables (with separate named ranges) and two separate pivot tables (the pivots each refer to the separate tables) all on the same sheet. For each of the pivot tables, I would like to have a separate pivot chart. In the pivot charts, when I click "Select Data", I find that the "Chart Data Range" for the pivot chart is grey (uneditable). Is there some setting that I need to toggle to enable the data source range so that I can change it? Also, when I try adding a new pivot chart to the sheet, this option is also grey/unavailable.
View 1 Replies
View Related
Aug 8, 2009
When I creating a Pivot Table Report on a different sheet. I have attached the Stock Report, where I have created a Button at the top to create the report, but the worksheet name is the default Excel name rather than the one that I want (in my case the name of the new sheet should be "Admin".
View 3 Replies
View Related
Mar 18, 2009
I'm getting a 1004 run-time error when creating a pivot table.
I believe this happens because I'm creating it in a hidden sheet. When I unhide it no errors occur.
View 2 Replies
View Related
Dec 19, 2011
I am looking to hide or delete the extra worksheet that is created when creating a Pivot table Macro. When I create a Pivot Table, it creates Sheet1 with it blank.
Is there a code that I can add to hide or delete the blank worksheet (sheet1)? Each workbook that the macro is distubuted in has a different workbook name, ex South_Report.XLSM, West_Report.XLSM.
View 4 Replies
View Related
Aug 29, 2012
I would like to know if it's possible to copy a pivot table from one sheet and paste it into in another sheet such that two pivot tables are not linked i.e. if I create a calculated item in pivot table it doesn't show as an item in another. If yes, how could I do that?
View 1 Replies
View Related
Jul 29, 2014
I'm trying to copy a certain cell range from a particular row of a pivot table to another sheet, in a loop but I do not successfully do the paste. There's no error, just no data gets pasted.
In my code currently i have:
Code:
Set Report = ActiveWorkbook.Sheets("Report")
Set Pivot = ActiveWorkbook.Sheets("Pivot").PivotTables(1)
dim size = Pivot.TableRange2.Rows.Count
for i =2 to size -1
Set currentRow = Pivot.TableRange2.Rows(i)
currentRow.Range(currentRow.Cells(1, 2), currentRow.Cells(1, 7)).Copy
Sheets("Report").Activate
ActiveSheet.Range("C" & 4 + i & ":H" & 4+ i).Select
Selection.Paste
next
Now, what does work if i select and paste the whole pivot row, but i don't want this. I've tried also with just referencing the Pivot table cells but also pasted nothing.
View 1 Replies
View Related
May 27, 2014
I have a portfolio of 23 funds and I need to calculate IRR for each one every month.
The problem is that I have dispersed information for each fund.
For instance in sheet CashCallsBD I have a table with the funds cash calls. This table has a first column with the Fund Name then other column with the cash call date, another column with the cash call value. This table may have other columns. One fund may have several cash calls.
In sheet DividendosBD there is a table with some columns that refer to cash distribution movements. The main columns relevant for this case are Fund Name, Date and finally Cash Distribution (signed in red in the attached document)
And then my problem: In order to calculate IRR for each fund I need to create a table (for each fund) that merges and sorts the information of CashCallsBD, Dividendos BD and present market value for each fund and then apply the XIRR function. For each fund this sorted table would have at least 2 columns (date and values).
View 3 Replies
View Related
Dec 8, 2011
I am trying to create a pivot table on a worksheet that already exists using VBA, but I am getting an error (Invalid procedure call or argument).
Code:
' Create the pivot table on a new tab
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
source_data).CreatePivotTable _
TableDestination:="DJG Clients with Multiple Fee E", TableName:="PT_ClientsMultipleFeeEarners", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
But if I just send the pivot table to a new sheet it works fine -
Code:
TableDestination:=""
View 2 Replies
View Related
Jan 21, 2012
I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.
View 9 Replies
View Related
Jul 18, 2013
When I filter my pivot table and I click on the button. The Filtered pivot table will automatically be copied to another sheet.
View 4 Replies
View Related
May 26, 2014
I am trying to link data from a pivot table we have in excel to a separate excel sheet.
The pivot table contains an up to date list of our inventory, however, we want to take that data and put it in a cleaner looking excel that we can e-mail to customers and publish on our website. Going individually, through every product takes too much time to have an up to date stock every day. Its even difficult to manage if we want to update just once a week.
When I link the cells of the pivot table to the new excel sheet they are constantly changing values as people are constantly editing the filters of the pivot table to look up various things.
View 6 Replies
View Related
Apr 13, 2004
I need to extract data from a spreadsheet given to me by my client, the best way to describe what I want to do is "a reverse of a pivot table"
The source sheet is arranged as follows:
Column A: Employee name every 3rd row
Column B: Normal Time on row 2, time and a half on row 3, double time on
row 4, normal time on row 5, time and a half on row 6 etc etc
Column C to I inclusive: Number of hours worked (on that day)
Column J: Subtotal of the 7 days (columns C to I)
Column K to Column DZ: Repeats of Column C to J
Row 1: dates in columns C to DY (except for the weekly subtotals)
I have many spreadsheets like this that I would prefer to have the information in a simple list format with the fields: Name, Date, Rate
Class (Normal Time, Time and a Half etc), Hours. Is there a simple/quick way to extract this information into the above mentioned fields? Am i being stupid and missing a simple solution?
View 9 Replies
View Related
Aug 12, 2008
In sheet1 (SA Awards) I have the source table for my pivot table in Range ("A1:G50"). In sheet2 (Team Listing) my pivot table is located in Range("K2:S13")
When I make changes in sheet1 I need my pivot table to update, I recorded a macro to refresh, however have only got it to work via a button & only if Sheet2 is unlocked
Sub PivotTableUpdate()
Sheets("Team Listing").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Sheets("SA Awards").Select
Range("B2").Select
End Sub
1. How can I get this to work in the Worksheet_Change Event?
2. How can I password protect Sheet2 & still have it work?
View 3 Replies
View Related
Feb 28, 2014
Is there a way I can compare sheet 1 Column A, with sheet 2 Column A, and then move any duplicates from sheet 2 over to sheet 3.
For instance,
Employee Numbers that match in both sheet 1 and 2, remove that row from sheet 2 to and insert it into sheet 3.
View 5 Replies
View Related
Mar 30, 2014
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
View 2 Replies
View Related
