Sort Data On Protected & Hidden Sheets

Sep 16, 2007

Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc

“The Range on each worksheet is the same ("A11:CR200") ....

View 9 Replies


ADVERTISEMENT

Sort Data When Sheet Is Protected?

Apr 15, 2014

What settings should I use in order to allow a user to sort data in a table when some of the cells are locked?

When I protected the sheet I ticked the option to allow user to sort data but it doesn't work...

View 2 Replies View Related

Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

Feb 28, 2014

I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.

View 7 Replies View Related

Hidden Rows In Protected Worksheet?

Jul 21, 2014

I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.

Macro command used:

Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"

View 3 Replies View Related

Opening Password Protected/hidden Worksheets By The Manager

Oct 4, 2007

I've looked through this wonderful sight and haven't found one close to my project. I am a bit rusty on VB programming. I am working on workbook that contains multiple worksheets. Each worksheet is hidden and is accessible with a login and password so that only a specific user can access. My question is: How do I modify this so that the manager can open the workbook and view ALL worksheets without having to enter the login name and password for all of his users? Any help would be great. Thank you!!

Below is what I have so far: ....

View 9 Replies View Related

Unable To Use Arrow Keys To Select Hidden Cells On A Protected Workbook

May 13, 2009

I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.

Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.

I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.

Does anyone know what might be causing this, or is the workbook corrupt?

View 9 Replies View Related

Delete Chart Series Across Sheets But Skip Protected Sheets

Mar 30, 2008

I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View 4 Replies View Related

Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets

Nov 10, 2008

i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

View 14 Replies View Related

Sort Raw Data Into Separate Sheets

Feb 10, 2012

I have a raw data worksheet with 54000 lines, in the format of six columns

In Column A is the USER Name

What I would like with if possible is code that will move the six columns of data from each individual USER into separate sheets, using Column A as the basis of the new sheets, ie the last row for each user is when the user name changes to the next one.

View 2 Replies View Related

Sort Data Spread Over Two Sheets

Jun 25, 2007

I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,

View 6 Replies View Related

Unable To Sort With Protected Sheet?

Dec 30, 2013

I have a sheet protected and filters enabled. I use this code to enable sort and filters:

[Code] ........

Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.

To make changes, click Unprotect Sheet in the Review tab (you might need a password)."

View 5 Replies View Related

Allow Sort, Sorting On Protected Worksheets

Aug 29, 2006

Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.

I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....

View 9 Replies View Related

Sort Macro On Protected Worksheet

Jun 8, 2008

I'm not fond with macros, but this is what i need help with.

I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).

This is what the macro to do. Unprotect, sort selected column, protect.

View 7 Replies View Related

Sort And Filter Range With Protected Cells

Nov 9, 2008

Is there any way to allow autofiltering and sorting even with locked cells within the range to be sorted? I've conditionally locked some cells based on user input, and at the end of my vb code I have allowsorting and allowfiltering. But I want to sort cells that are locked even after that. Does anyone know if that's even possible with VB?

View 4 Replies View Related

AutoFilter And Sort Not Working On Protected Sheet

Dec 20, 2012

When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:

Code:

With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With

I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.

View 1 Replies View Related

Allow Sorting: Protected Worksheet Doesn't Allow A Sort

Sep 11, 2006

I have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.

View 2 Replies View Related

Very Hidden Sheets Are Very Hidden

Jan 2, 2007

i have an old spreadsheet that i created about a year or so ago.
i managed to make some sheets "very hidden" as opposed to just "hidden" or "visible".

the problem is i can't find them ! i have tried "format, sheet, unhide" but that is greyed out.

i do have passwords for them, but i can't find them to put these in!

View 9 Replies View Related

Sort Protected Worksheet Excluding Blank Cells

Apr 11, 2005

I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.

Now, I have a macro which seems to do the job: ....

View 9 Replies View Related

Error On Protected Sheet Even If Autofilter And Sort Boxes Are Ticked

Feb 4, 2012

If I protect a sheet but tick the boxes to allow sorting and autofilter I still get an erro when I try to use the sort buttons on the autofilter.

It allows me to use the filters but as soon as I sort I get told to unprotect the cells im trying to sort.

Is there a way around this where I can keep formulas from being touched but allow sorting of data through autofilter?

View 3 Replies View Related

Copying From Hidden Cells Are Not Copying Protected

Dec 12, 2007

I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-

View 12 Replies View Related

Hidden Sheets

Oct 3, 2008

I have a 15 sheet spreadsheet that has many formula and results that are obtained from a "Master Sheet." I want to hide the "Master Sheet" from users view.

When I do so the formulae can't find the info they require. How do I hide a sheet so that users can't see it, but the formula from other sheets can still access it?

View 4 Replies View Related

VBA With Hidden Sheets

Dec 5, 2007

i've an excel file where a final presentable sheet is linked to some hidden sheets which i dont want the user to see. Thru VBA, i'm performing some format related stuff in those hidden sheets, using: Sheets("Historical1").Visible = True. later, VBA sets it back to FALSE. But, i want to get rid of this hiding/unhiding, if its possible. My code has the following structure:

Sub tame_blph1()
Sheets("Historical1").Visible = True
Sheets("Historical1").Activate
Dim coll As Integer
Dim startcoll As Integer
Dim endcoll As Integer
startcoll = 25
endcoll = 124
For coll = startcoll To endcoll Step 2
If Len(selection) > 0 Then Call Macro1
Next coll
Sheets("Historical1").Visible = False
Sheets("Table").Select
End Sub

In other words, can we perform the above without hiding and unhiding the Sheet called "Historical1"

View 3 Replies View Related

How To Password Protected All Sheets At Once

May 15, 2014

i want to protect my all sheets at once

View 3 Replies View Related

Macros On Protected Sheets

Nov 21, 2006

I have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.

View 3 Replies View Related

Hyperlinks To Hidden Sheets?

Jun 15, 2013

I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.

View 3 Replies View Related

Hidden Sheets Within An Workbook

Nov 18, 2009

I have been sent a workbook which contains a number of formulae that reference hidden worksheets (see attached JPEG).

I cannot work out how to display these sheets? The workbook does not seem to be protected, I can edit and save the visible sheet. Can anyone shed some light on how to view the referenced sheets?

View 4 Replies View Related

Selecting Hidden Sheets

Sep 2, 2009

I have the following code which works fine when all of the sheets are not hidden:

View 3 Replies View Related

Hyperlink To Hidden Sheets

Apr 5, 2013

I have a total of 15 sheets on my excel documents. I've hidden 10 (5 sheets will be shown) of those sheets so i can hyperlink to them from the sheet called "The plan". The name of the hidden sheets are called:

"Week 1"
"Week 2"
"Week 3"
"Week 4"
"Week 5"
"Week 6"
"Week 7"
"Week 8"
"Week 9"
"Week 10"

On "The plan" I have hyperlinked the words to the corresponding sheets then I hid the sheets, now the hyperlinks don't work. What should I do?

View 2 Replies View Related

VBA To Delete All Hidden Sheets?

Aug 21, 2013

I just came across file that needs to be cleaned up. Problem is, the main summary references more than 50 hidden sheets for pay periods dating back to 2011.

I want to make everything values from previous pay periods then get rid of the hidden sheets.

View 4 Replies View Related

Macros And Hidden Sheets

Jul 31, 2007

ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved