Using a variable to select a cell on a worksheet..."Subscript outor range"
On May 28, 10:00*am, Zarlot wrote:
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.
2 things. You have ws1 declared as a Worksheet, but you are trying to
assign a range to it (.Cells(3,2) from the WhichSheetCell variable).
WhichSheetCell is a variable, but you have quotes around it in your
Set ws1 statement.
I'm not exactly sure where the Cells(3,2) is coming into play, but
here is a snippet you can use for an example.
Dim ws1 As Worksheet, rng As Range
Set ws1 = Workbooks.Open("Z:\Invoices\test.xls") _
.Worksheets("Sheet1")
Set rng = ws1.Range("A1:N" & Rows.Count)
|