PDA

View Full Version : [SOLVED] Writing to an xlveryhidden sheet



gibbo1715
09-14-2005, 12:40 PM
another quiestion i probably should know the answer to by now.

I am using a userform to display the contents of an excel spreadsheet that i keep very hidden, i currently unhide it paste my data and then rehide the sheet again within my code.

Can anyone give me a basic example of how to write too a very hidden sheet and read from it without the need to make it visible hopefully speeding up my code?

Thanks

Gibbo

mvidas
09-14-2005, 12:45 PM
Hi Gibbo,

Rather than use copy/paste, you could use something like:


Dim TheVeryHiddenSheet As Worksheet, TheVisibleSheet As Worksheet
Set TheVeryHiddenSheet = Sheets("Sheet2")
Set TheVisibleSheet = Sheets("Sheet1")
TheVeryHiddenSheet.Range("A1:A30").Value = TheVisibleSheet.Range("A1:A30").Value


Matt

Jacob Hilderbrand
09-14-2005, 12:46 PM
Just specify the sheet name before the range. In fact you do not even need to make it visible to paste.


Sheets("Sheet1").Range("A1").Value = 1
Sheets("Sheet2").Range("A1:B10").Copy Destination:=Sheets("Sheet1").Range("A1")


In these examples, Sheet1 could be hidden or very hidden and it would not matter.

gibbo1715
09-14-2005, 12:48 PM
so if i set the work sheet before carrying out any of my actions actions on it i will never need to make it visible, to search, read or update, is that correct?

gibbo1715
09-14-2005, 12:49 PM
sorry posted before your reply Jake, I ll have a play with both methods, thanks

Gibbo

mvidas
09-14-2005, 12:56 PM
Jake,
Though I can't duplicate it now, I had a problem once using the very hidden sheet in an Destination argument. May have been unrelated though.

Gibbo,
We're here if you need us!

gibbo1715
09-14-2005, 01:02 PM
Thanks Again

I have learnt loads since joining this forum and am just starting to be able to offer the odd bit of advice to others (Although only the odd bit at the moment)

Your help is greatly appreciated

Gibbo:beerchug:

Jacob Hilderbrand
09-14-2005, 01:12 PM
I double checked the code and it works fine on a very hidden sheet.

Yes, if you specify the sheet name, you do not have to activate it or make it visible.

You're Welcome :beerchug:

Take Care