Set bk = Workbooks(...)

[R Dunn]

Hi all,

In my macro, I want to use expressions like:

Do Until (Sheet1.Cells(startRow, 2).Value = 5)

But I'm having trouble "setting up" Sheet1. I've got the declaration
statements

Dim Sheet1 As Worksheet
Dim bk As Workbook

But then for the "Set" part, I'm not sure how to go about it. I've tried
(and subsequently commented out) all of the following. Can anyone see where I
am going wrong?
(The file name is quite long - Chessel 0 deg June 25th 200-275 plus 575.xls)

'Set bk = ThisWorkbook
'Set bk = Workbooks("Chessel 0 deg June 25th 200-275 plus 575")
Set bk = Workbooks("CHESSEL 0 DEG JUNE 25TH 200-275 PLUS 575")
Set Sheet1 = bk.Worksheets("TEMPDATA")
'Set Sheet1 = Worksheets("TEMPDATA")


Thanks.

Rebecca

 

[Tom Ogilvy]

Dim bk as Workbook
Dim sh as Worksheet

set bk = workbooks("Chessel 0 deg June 25th 200-275 plus 575.xls")
set sh = bk.Worksheets("Tempdata")

sheet1 could conflict with the code name of one of you sheets.

Use the .xls extension.