Get values from hidden sheets? 3

[VulcanJedi]

I tried cells() but code crashes when accessing a hidden sheet, anyway aroung this? Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[ETID]

Have you tried the full path...


=sheets("sheet_name&quot.cells(y,x)

 

[VulcanJedi]

well I am talking from within the VBA code itself and yes full path name. Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[ETID]

could there be protection on that hidden sheet?

 

[xlbo]

What are you trying to do with this hidden sheet ???
The safest option is to enclose any hidden sheet shenanigans in a WITH statement. You are then accessing it IMplicitly rather than EXplicitly Rgds
~Geoff~

 

[RobBroekhuis]

No reason why it shouldn't work. I'm always accessing data from worksheets in my AddIns, which are hidden.
Rob

 

[CharlesCook]

This is the first thing I do after I open the .xls file

For Each sh In Sheets
sh.Visible = True
Next sh CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[VulcanJedi]

My apologies I had an existing 'app'. Part of it works fine. however the current code says to switch sheets ie sdheets(2).select then sort
this is when it crashes. I can't seem to get the sheet to sort without using select?
Also is there any way anyone would know sheets are hidden if you didnt' tell them, and pwd the code? I can't see how? Thanks. Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[VulcanJedi]

my code below basically I have a page of locations, I want to via optionbutton sort by supplier code or alphabetically...

myrng = "A1:H" & GetMaxRows(Sheets("Locations&quot)
wksht("Locations&quot.Range(myrng).Sort Key1:=Range("B2&quot, Order1:=xlAscending, Key2:= _
Range("A2&quot, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[RobBroekhuis]

By golly you're right - the sort method doesn't seem to work on a range that's not on the active sheet. Sounds like a bug to me...
And indeed, you cannot activate or select a hidden sheet, so you would have to first set the visible property to true, then activate the sheet, sort the range, and the hide the sheet again. Not too terrible, if you have ScreenUpdating set to false.
Rob

 

[xlbo]

To sort on a hidden sheet, use something like this:
With Sheets("sheet1&quot.Columns("A:B&quot
.Sort Key1:=.Range("A2&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

The key1 ref is important - make sure it says .Range rather than just Range Rgds
~Geoff~

 

[RobBroekhuis]

Very strange - I had used the sort method without specifying the key1 parameter, so I thought I would have avoided that complication - but you're right:

sheets("sheet2&quot.range("A1:A4&quot.sort sheets("sheet2&quot.range("A1&quot

works, while

sheets("sheet2&quot.range("A1:A4&quot.sort

doesn't (Sheet2 is the hidden sheet). Still sounds like a bug to me.
Rob

 

[xlbo]

The problem is, as you say that sort needs an active reference. You can only get an active reference from a hidden sheet by using the WITH statement 'cos Select and Activate don't work on a hidden sheet. the code I posted above works (tested) on a hidden sheet Rgds
~Geoff~

 

[VulcanJedi]

Thanks everyone, Geoff, I tried your code and it didn't seem to work? Anyways the simplest thing I could get to work was to unhide all the hidden sheets exploiting the screen updating and then everything works, I'd still be very interesting in figuring out the WITH statement implementation however. Thanks again, most response ever for any of my threads! Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[RobBroekhuis]

xlbo,
You don't really NEED to use a with statement. The first syntax in my post above works (yes, it looks ugly) without a with statement, regardless of whether the sheet is hidden. Your code, of course, looks more elegant, especially if there are multiple keys.
Rob