Problem referencing other workbooks

[KL_UK]

Hi,

A bit of a Newb question here....

I'm trying to get the user to input the name of a source workbook and then use this throughout my code, but I've fallen at the first hurdle. This is my code:

Dim SourceWorkbook As String
Dim SourceSheet As String
Dim SourceLocation As Workbook

SourceWorkbook = InputBox("Enter the filename of the source spreadsheet", "Source Spreadsheet")
SourceSheet = InputBox("Enter the worksheet name of the source spreadsheet", "Source Worksheet")

Set SourceLocation = Workbooks(SourceWorkbook).Worksheets(SourceSheet)

'Test output
MsgBox (SourceLocation.Cells(1, 1).Value)

' lots of other stuff....


This returns an error. The problem seems to be the type of SourceLocation.

Can somebody please put me straight - how do I reference the workbook once I've got the input?

Many thanks,
Kris

[Norie]

Kris

Well it could be, especially when you appear to be trying to set a reference to a worksheet when SourceLocation is declared as a workbook.

[KL_UK]

So close!

Thanks,
Kris