Use of Cell Values Instead of Hard Coding

[GEdwards]

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!

 

[Rick Rothstein]

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)

 

[ker_01]

You are very close;

When you pass WBook as a parameter, Excel will use the string that it
represents. When you pass "WBook", then Excel simply takes that string, and
does not know that you are trying to refer to a variable.

Set MstrInvList = Workbooks(WBook) and
Set AllInvSht = Worksheets(WSheet)

HTH,
Keith

 

[GEdwards]

I tried that but I get

Run-time error '9':

Subscript out of range

 

[GEdwards]

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

Run-time error '9':

Subscript out of range

 

[Dave Peterson]

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]

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