ExcelBanter - Declaring Worksheets

I use something similar, however I use it as a sub of its own that i
call when the program launches.

Public Sub SetSheets(x As Byte)
'this runs each time the UI is shown to set the sheet names variables
Set shtCover = ThisWorkbook.Sheets("Budget Creator")
Set shtBudget = ThisWorkbook.Sheets("Budget")
Set shtDetail = ThisWorkbook.Sheets("Detail")
Set shtVar = ThisWorkbook.Sheets("Budget Variables")
Set shtBNotes = ThisWorkbook.Sheets("Build Notes")
Set shtBugList = ThisWorkbook.Sheets("Bug List")

End Sub

and I place the following in the GlobalVariable module

'Worksheet Variables
' for variable assignments look for the SetSheets sub in Module1
Public shtCover As New Worksheet
Public shtBudget As New Worksheet
Public shtDetail As Worksheet
Public shtVar As New Worksheet
Public shtBNotes As New Worksheet
Public shtBugList As New Worksheet

It works for me in most cases.

theSquirrel

Bob Phillips wrote:
I would include the workbook object

With ActiveWorkbook
Set wsQty = .Worksheets(1)
Set wsSales = .Worksheets(2)
Set wsfinal = .Worksheets(3)
End With

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"RSteph" wrote in message
...
I have an excel workbook with 3 worksheets in it. I'm looking to do some
formatting to the first two pages, and then pull some information from
each
of them to the third page, to create a final report.

for ease of navigation between each page in the code, I'm made 3 variables
of type worksheet, one to hold each sheet. I've opened external sheets
before, from other workbooks, but never within the same workbook, so I
wanted
to double check that I'm doing this right.

Can I simply instantiate my variables like this?:
Set wsQty = Worksheets(1)
Set wsSales = Worksheets(2)
Set wsfinal = Worksheets(3)

Assuming that the first page in the quantities, 2nd is sales, and 3rd page
is where the final report will go.