Referring to the only sheet

[Otto Moehrbach]

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

 

[Norman Jones]

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
'<<=============

 

[Guest]

Hi Otto,

try

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

 

[Otto Moehrbach]

Norman, Vergel
Such a simple thing! Thanks for taking the time to help me with this.
Otto