Zarlot,

Change

Set ws1 = Sheets("WhichSheetCell")

to

Set ws1 = Sheets(WhichSheetCell)

(Remove the double quotes.)

Still, you will probably need some error checking in there...

HTH,
Bernie
MS Excel MVP


"Zarlot" wrote in message
...
See code below. I know this is pretty simple, but what I'm trying to
do is pull data from a cell (in the test.xls spreadsheet) that has a
varying (based upon what someone writes in the cell) text input. I
then want the macro I run to reference this cell to select which
worksheet it is going to select in the spreadsheet in which I'm
running the macro.




Dim rng As RangeDim ws1 As Worksheet
Dim WhichSheetCell As String
WhichSheetCell = Workbooks.Open("Z:\Invoices
\test.xls").Worksheets("Sheet1").Cells(3, 2)
Set ws1 = Sheets("WhichSheetCell")


Set rng = ws1.Range("A1:N" & Rows.Count)

I get a "subscript out of range" error when I run it.