Operating on hidden sheets
 

Hi,

Let's say I have a "Sheet1" which is hidden.

Programmatically WITHOUT UNHIDING the above sheet, I go ahead and write
sheet1.activate
Range(Cells(1, 1), Cells(5000, 1)).Select
ActiveSheet.Paste

The debugger does not give me any error at the statement --
sheet1.activate -- but neither it is activating sheet1, rather a sheet
(sheet2)which is already active is selected and within sheet2's range
(A1:A5000) the data is pasted.

Why is it behaving like this? Either it should paste the data in Sheet1 or
it should give me an error saying that a hidden sheet cannot be activated.
Is there a logic for the present behaviour?

(Pls. note I can programmatically unhide the sheet, do the desired copy,
paste operation and then again hide it but my question is from understanding
point of view)

Thanks a lot,
Hari
India

Hi

But whi to activate the sheet at all?

....
Sheets("Sheet2").Range("A1").Offset(5000, 0).Copy
Sheets("Sheet1").Range("A1").Offset(5000, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Hari Prasadh" wrote in message
...
Hi,

Let's say I have a "Sheet1" which is hidden.

Programmatically WITHOUT UNHIDING the above sheet, I go ahead and write
sheet1.activate
Range(Cells(1, 1), Cells(5000, 1)).Select
ActiveSheet.Paste

The debugger does not give me any error at the statement --
sheet1.activate -- but neither it is activating sheet1, rather a sheet
(sheet2)which is already active is selected and within sheet2's range
(A1:A5000) the data is pasted.

Why is it behaving like this? Either it should paste the data in Sheet1 or
it should give me an error saying that a hidden sheet cannot be activated.
Is there a logic for the present behaviour?

(Pls. note I can programmatically unhide the sheet, do the desired copy,
paste operation and then again hide it but my question is from
understanding
point of view)

Thanks a lot,
Hari
India

Try this:

With Worksheets("Sheet1")
Range(.Cells(1, 1), .Cells(5000, 1)).PasteSpecial
End With

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Hari Prasadh" wrote in message
...
Hi,

Let's say I have a "Sheet1" which is hidden.

Programmatically WITHOUT UNHIDING the above sheet, I go ahead and write
sheet1.activate
Range(Cells(1, 1), Cells(5000, 1)).Select
ActiveSheet.Paste

The debugger does not give me any error at the statement --
sheet1.activate -- but neither it is activating sheet1, rather a sheet
(sheet2)which is already active is selected and within sheet2's range
(A1:A5000) the data is pasted.

Why is it behaving like this? Either it should paste the data in Sheet1 or
it should give me an error saying that a hidden sheet cannot be activated.
Is there a logic for the present behaviour?

(Pls. note I can programmatically unhide the sheet, do the desired copy,
paste operation and then again hide it but my question is from
understanding point of view)

Thanks a lot,
Hari
India