Referring to the only sheet
 

Excel XP & Win XP
This is very simple but I can't quite hit it.
I have a variable workbook set to the variable "wbCMP". That workbook has
only one sheet and the sheet name is variable. That workbook is NOT the
active workbook. I want to refer to that single sheet in a With .../End
With construct, as in:
With wbCMP.Sheet1
'Copy/paste data
End With
I tried .Sheets(Sheet1)
..Sheets(Sheet(1))
..Sheet(1)
to no avail.
What is the proper syntax to refer to that lone sheet? Thanks for your
time. Otto

Referring to the only sheet
 

Hi Otto,

Try:

'=============
Public Sub Tester()
Dim wbCMP As Workbook
Dim SH As Worksheet

Set wbCMP = Workbooks("MyBook.xls")

Set SH = wbCMP.Sheets(1)
MsgBox SH.Name

End Sub
'<<=============


---
Regards,
Norman


"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
This is very simple but I can't quite hit it.
I have a variable workbook set to the variable "wbCMP". That workbook has
only one sheet and the sheet name is variable. That workbook is NOT the
active workbook. I want to refer to that single sheet in a With .../End
With construct, as in:
With wbCMP.Sheet1
'Copy/paste data
End With
I tried .Sheets(Sheet1)
.Sheets(Sheet(1))
.Sheet(1)
to no avail.
What is the proper syntax to refer to that lone sheet? Thanks for your
time. Otto

Referring to the only sheet
 

Hi Otto,

try

With wbCMP.Sheets(1)
msgbox .Name 'pop up the sheet name
End With


--
Hope that helps.

Vergel Adriano


"Otto Moehrbach" wrote:

Excel XP & Win XP
This is very simple but I can't quite hit it.
I have a variable workbook set to the variable "wbCMP". That workbook has
only one sheet and the sheet name is variable. That workbook is NOT the
active workbook. I want to refer to that single sheet in a With .../End
With construct, as in:
With wbCMP.Sheet1
'Copy/paste data
End With
I tried .Sheets(Sheet1)
..Sheets(Sheet(1))
..Sheet(1)
to no avail.
What is the proper syntax to refer to that lone sheet? Thanks for your
time. Otto

Referring to the only sheet
 

Norman, Vergel
Such a simple thing! Thanks for taking the time to help me with this.
Otto
"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
This is very simple but I can't quite hit it.
I have a variable workbook set to the variable "wbCMP". That workbook has
only one sheet and the sheet name is variable. That workbook is NOT the
active workbook. I want to refer to that single sheet in a With .../End
With construct, as in:
With wbCMP.Sheet1
'Copy/paste data
End With
I tried .Sheets(Sheet1)
.Sheets(Sheet(1))
.Sheet(1)
to no avail.
What is the proper syntax to refer to that lone sheet? Thanks for your
time. Otto