Dave,
You were right in having me find what the active Workbook/sheet were. Once
I set it to the correct one, all is fine.

Thanks to all.

Garth

"Dave Peterson" wrote:

Dim MstrInvList As Workbook
Dim AllInvSht As Worksheet

set WBook = workbooks(Range("S2").value)
set WSheet = wbook.worksheets(Range("R2").value)

But if you get that subscript out of range, then the names in S2 and R2 don't
match up with the names of the open workbooks.

I'd do some testing and also qualify those ranges!

Dim MstrInvList As Workbook
Dim AllInvSht As Worksheet

set wbook = nothing
on error resume next
set WBook = workbooks(activesheet.Range("S2").value)
on error goto 0

if wbook is nothing then
msgbox "There is no open workbook with that name!"
exit sub '???
end if

set wsheet = nothing
on error resume next
set WSheet = wbook.worksheets(activesheet.Range("R2").value)
on error goto 0

if wsheet is nothing then
msgbox "The workbook was found, but it didn't have a sheet with that name!"
exit sub '???
end if


=======
I used the activesheet, but you could use something like:

set WSheet _
= wbook.worksheets(thisworkbook.worksheets("Sheet999 ").Range("R2").value)


GEdwards wrote:

I am using Excel 2003 and want to make my code as generic as possible.

I am very junior with this but I know proper coding is ...

Dim MstrInvList As Workbook
Dim AllInvSht As Worksheet

Set MstrInvList = Workbooks("Invoices & Work Estimates.xls")
MstrInvList.Activate
Set AllInvSht = Worksheets("Invoices & Work Estimates")
AllInvSht.Activate

But I would like something like this...
where cell S2 contains "Invoices & Work Estimates"
and cell R2 contains "Invoices & Work Estimates.xls"

The SET lines below fail. Is there a way around this? Can cell values used
in place of hard coding the workbook, worksheet names, etc?

Dim MstrInvList As Workbook
Dim AllInvSht As Worksheet

WBook = Range("S2")
WSheet = Range("R2")

Set MstrInvList = Workbooks("WBook")
MstrInvList.Activate
Set AllInvSht = Worksheets("WSheet")
AllInvSht.Activate

Others that I need to consider are working with these others...
Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls")
Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8")
Set DestSh = DestWB.Worksheets("Invoices & Work Estimates")

Any and all help would be appreciated. Thanks in advance!

--

Dave Peterson
.