Writing to an xlveryhidden sheet

[gibbo1715]

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]

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]

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]

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]

sorry posted before your reply Jake, I ll have a play with both methods, thanks

Gibbo

[mvidas]

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]

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

[Jacob Hilderbrand]

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

Take Care