Operating on hidden sheets

[Hari Prasadh]

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

[Rob van Gelder]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Arvi Laanemets]

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<at>tarkon.ee
Arvi Laanemets


"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>