Why can't I define my workbook as an object?
It's actually a sensible question. Here's the answer from Excel 2010 help:
"The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel."
So, since that workbook isn't open - at least I assume it isn't - it can't be set as a workbook object. If it was open you'd just set it like:
Set wbk = workbooks("Master Benchmark Data Sheet.xlsx")
You'll need to open the workbook to refer to it.
Sub Setwbk() Dim wbk As Workbook Set wbk = Workbooks.Open("F:\Quarterly Reports\2012 Reports\New Reports\ _ Master Benchmark Data Sheet.xlsx")End Sub
* Follow Doug's answer if the workbook is already open. For the sake of making this answer as complete as possible, I'm including my comment on his answer:
Why do I have to "set" it?
Set
is how VBA assigns object variables. Since a Range
and a Workbook
/Worksheet
are objects, you must use Set
with these.