I have a workbook with some hidden sheets, which the user selects to unhide
as required. I wanted to use a macro button to copy all pages except page 1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all pages
hidden or unhidden to a new workbook. However when I got to work and tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the copy
code above, but to no avail. I guess this functionality was only built into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

I don't use xl97 (and your code worked ok for me in xl2002).

But are you sure that the names of the worksheets match your array's list?

"Sheet 3" looks out of place ("sheet3"???)

(That extra space looks weird.)

el_peacock wrote:

I have a workbook with some hidden sheets, which the user selects to unhide
as required. I wanted to use a macro button to copy all pages except page 1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all pages
hidden or unhidden to a new workbook. However when I got to work and tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the copy
code above, but to no avail. I guess this functionality was only built into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

--

Dave Peterson

Hi
Yes, sorry my sheets actually have different names than sheet1, sheet2 etc -
I just put that in for the example. Well spotted though! The array
definitely matches the sheet names.

Thanks

"Dave Peterson" wrote:

I don't use xl97 (and your code worked ok for me in xl2002).

But are you sure that the names of the worksheets match your array's list?

"Sheet 3" looks out of place ("sheet3"???)

(That extra space looks weird.)

el_peacock wrote:

I have a workbook with some hidden sheets, which the user selects to unhide
as required. I wanted to use a macro button to copy all pages except page 1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all pages
hidden or unhidden to a new workbook. However when I got to work and tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the copy
code above, but to no avail. I guess this functionality was only built into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

--

Dave Peterson

In Design mode, try changing TakeFocusOnClick to false. I think this is an
issue in XL97, corrected in later versions.

Regards,
Peter

"el_peacock" wrote in message
...
I have a workbook with some hidden sheets, which the user selects to
unhide
as required. I wanted to use a macro button to copy all pages except page
1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all
pages
hidden or unhidden to a new workbook. However when I got to work and
tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the
copy
code above, but to no avail. I guess this functionality was only built
into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

In Design mode, try changing TakeFocusOnClick to false. After doing this
your code worked for me in xl97. This appears to be an issue in XL97,
corrected in later versions.

To cater for versions, maybe something like this in your Auto_Open

MySheet.OLEObjects("CommandButton2").Object.TakeFo cusOnClick =
(Val(Application.Version) 8)

Regards,
Peter

"el_peacock" wrote in message
...
I have a workbook with some hidden sheets, which the user selects to
unhide
as required. I wanted to use a macro button to copy all pages except page
1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all
pages
hidden or unhidden to a new workbook. However when I got to work and
tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the
copy
code above, but to no avail. I guess this functionality was only built
into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

Good answer.

But maybe simpler would be to put this near the top of the routine:

activecell.activate



Peter T wrote:

In Design mode, try changing TakeFocusOnClick to false. After doing this
your code worked for me in xl97. This appears to be an issue in XL97,
corrected in later versions.

To cater for versions, maybe something like this in your Auto_Open

MySheet.OLEObjects("CommandButton2").Object.TakeFo cusOnClick =
(Val(Application.Version) 8)

Regards,
Peter

"el_peacock" wrote in message
...
I have a workbook with some hidden sheets, which the user selects to
unhide
as required. I wanted to use a macro button to copy all pages except page
1
to a new workbook. I used the following code assigned to a button on page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all
pages
hidden or unhidden to a new workbook. However when I got to work and
tried
it (on excel 97), I got a runtime error. It's as if I can't copy hidden
sheets. I tried selecting the sheets with a line of code prior to the
copy
code above, but to no avail. I guess this functionality was only built
into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

--

Dave Peterson

Yes, simpler and a better answer <g

I suppose could also do a version check within the event routine if needs.
On which point would be better to write as:
(Val(Application.Version) = 9), rather than " 8"

Sorry about my earlier double post. Really thought I had cancelled the
first, having thought I ought check the suggestion would work.

Regards,
Peter

Dave Peterson wrote:
Good answer.

But maybe simpler would be to put this near the top of the routine:

activecell.activate

Peter T wrote:

In Design mode, try changing TakeFocusOnClick to false. After doing this
your code worked for me in xl97. This appears to be an issue in XL97,
corrected in later versions.

To cater for versions, maybe something like this in your Auto_Open

MySheet.OLEObjects("CommandButton2").Object.TakeFo cusOnClick =
(Val(Application.Version) 8)

Regards,
Peter

"el_peacock" wrote in message
...
I have a workbook with some hidden sheets, which the user selects to
unhide
as required. I wanted to use a macro button to copy all pages except
page
1
to a new workbook. I used the following code assigned to a button on
page
one:

Private Sub CommandButton2_Click()

Sheets(Array("Sheet2", "Sheet 3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Copy

End Sub

It worked absolutely fine in my pc at home (excel 2000) and copied all
pages
hidden or unhidden to a new workbook. However when I got to work and
tried
it (on excel 97), I got a runtime error. It's as if I can't copy
hidden
sheets. I tried selecting the sheets with a line of code prior to the
copy
code above, but to no avail. I guess this functionality was only
built
into
excel 2000, but is there any way of achieving it in excel 97?
--
Thanks!
---------
Gareth

--

Dave Peterson