Hi,

I 'm still using Excel 97 and have this sub in a module.

Public Sub CellsOnInactiveSheet()
With Sheets("Sheet1")
.Range(.Range("A1"), .Range("B2")).Select
End With
End Sub

If any sheet except Sheet1 is active this produces an error.
I would have thought with a fully qualified address it should work with any
sheet active.

I don't really want to activate the sheet if I can help it.
Any help appreciated, thanks,
John Duffus

Perhaps the .Select property cannot cross-over sheets;
it is restricted to only the sheet where code is running.


"John Duffus" wrote in message
news:g%s6f.259490$1i.7890@pd7tw2no...
Hi,

I 'm still using Excel 97 and have this sub in a module.

Public Sub CellsOnInactiveSheet()
With Sheets("Sheet1")
.Range(.Range("A1"), .Range("B2")).Select
End With
End Sub

If any sheet except Sheet1 is active this produces an error.
I would have thought with a fully qualified address it should work with
any sheet active.

I don't really want to activate the sheet if I can help it.
Any help appreciated, thanks,
John Duffus

John

You cannot select a cell on an inactive sheet

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"John Duffus" wrote in message
news:g%s6f.259490$1i.7890@pd7tw2no...
Hi,

I 'm still using Excel 97 and have this sub in a module.

Public Sub CellsOnInactiveSheet()
With Sheets("Sheet1")
.Range(.Range("A1"), .Range("B2")).Select
End With
End Sub

If any sheet except Sheet1 is active this produces an error.
I would have thought with a fully qualified address it should work with
any sheet active.

I don't really want to activate the sheet if I can help it.
Any help appreciated, thanks,
John Duffus

Thanks Jim,
I can use the same addressing to read the range into an
array, regardless of which sheet is active, so it is my use of the Select
property that seems to be the problem.
John Duffus

"Jim May" wrote in message
news:Rtt6f.5059$AO5.1394@dukeread01...
Perhaps the .Select property cannot cross-over sheets;
it is restricted to only the sheet where code is running.


"John Duffus" wrote in message
news:g%s6f.259490$1i.7890@pd7tw2no...
Hi,

I 'm still using Excel 97 and have this sub in a module.

Public Sub CellsOnInactiveSheet()
With Sheets("Sheet1")
.Range(.Range("A1"), .Range("B2")).Select
End With
End Sub

If any sheet except Sheet1 is active this produces an error.
I would have thought with a fully qualified address it should work with
any sheet active.

I don't really want to activate the sheet if I can help it.
Any help appreciated, thanks,
John Duffus

Thanks Nick,
Problem solved
John D.
"Nick Hodge" wrote in message
...
John

You cannot select a cell on an inactive sheet

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"John Duffus" wrote in message
news:g%s6f.259490$1i.7890@pd7tw2no...
Hi,

I 'm still using Excel 97 and have this sub in a module.

Public Sub CellsOnInactiveSheet()
With Sheets("Sheet1")
.Range(.Range("A1"), .Range("B2")).Select
End With
End Sub

If any sheet except Sheet1 is active this produces an error.
I would have thought with a fully qualified address it should work with
any sheet active.

I don't really want to activate the sheet if I can help it.
Any help appreciated, thanks,
John Duffus