Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referring to a sheet in another file | Excel Worksheet Functions | |||
Formula referring to a different sheet | Excel Discussion (Misc queries) | |||
Referring to a sheet | Excel Programming | |||
Referring to a cell in another sheet | Excel Programming | |||
referring to previous sheet | Excel Discussion (Misc queries) |