As I posted to ker_01, I tried that but I get

Run-time error '9':

Subscript out of range


"Rick Rothstein" wrote:

WBook and WSheet are variables... you don't put variable names inside of
quote marks if you want to get at their contents (text inside of quote marks
is just that... text). Try your Set statements this way...

Set MstrInvList = Workbooks(WBook)

Set AllInvSht = Worksheets(WSheet)

--
Rick (MVP - Excel)



"GEdwards" wrote in message
...
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!

.