Help with VBA runtime error

[Zilla]

I get runtime error - 1004 Method 'Range' of object _Worksheet
failed during the Set sRange line. I even tried to hard code row/col
values (next commented line) and get the same error. Please advise.
Thanks!

' Get the info for the RESULTNAME block from ONE
' of the sheets; I'll be copying 4 sets of row:col
' data so I defined the rowArray(4) and colArray(4)
Sub getResultInfo()
Dim baseBook As Workbook
Dim sheet1 As Worksheet ' destination
Dim sheet2 As Worksheet ' source
Dim sRange As Range ' source
Dim dRange As Range ' dest
Dim colVals(4) ' column numbers to be copied
Dim destRow As Integer
Dim destCol As Integer
Const srcRow = 99 ' row number to be copied

' Brute force assignment of columns to
' be copied
colVals(1) = 2
colVals(2) = 5
colVals(3) = 11
colVals(4) = 12

destRow = resRow + 1 ' resRow obtained in writeHeaders1()
destCol = resCol + 1
Application.ScreenUpdating = False
Set baseBook = ThisWorkbook
Set sheet1 = baseBook.Sheets(1)
Set sheet2 = baseBook.Sheets(2)

'Copy RESULTNAME
Set sRange = sheet2.Range(Cells(srcRow, colVals(1)), _
Cells((srcRow + 16), colVals(1)))
' Set sRange = sheet2.Range(Cells(99, 2), Cells(114, 2))

Set dRange = sheet1.Range(Cells(destRow, destCol), _
Cells(destRow, (destCol + 16)))
Call copyRange(sRange, dRange)
End Sub

 

[Jason Lepack]

Tell it which sheet you want the cells from:

Set sRange = Sheet2.Range(Sheet2.Cells(srcRow, colVals(1)), _
Sheet2.Cells((srcRow + 16), colVals(1)))

Cheers,
Jason Lepack

 

[Zilla]

But doesn't the "sheet2.Range()" call effectively do that since it
refers to the sheet2 object?

Anyway the call worked by "activating" the sheets first before calling
the Range method.

 

[Jim Thomlinson]

Any range object such as Range or Cell that is not explicitly referenced to a
sheet will default to the active sheet. What you have is equivalent to...

Set sRange = sheet2.Range(Activesheet.Cells(srcRow, colVals(1)), _
Activesheet.Cells((srcRow + 16), colVals(1)))

Which is obviously wrong. By activating the sheet prior to calling this line
your active sheet is sheet 2 so it works. That being said I would still be
inclined to go with Jason's suggested code as it works regardless of which
sheet is active. Or a little cleaner and a tad more efficient would be.
With sheet2
Set sRange = .Range(.Cells(srcRow, colVals(1)), _
.Cells((srcRow + 16), colVals(1)))
End with

 

[Jason Lepack]

As I see that Jim Thomlinson has replied, I'll echo his statements.

In Jim's example:
Set sRange = sheet2.Range(Activesheet.Cells(srcRow, colVals(1)), _
Activesheet.Cells((srcRow + 16),
colVals(1)))

The reason you get the error is that you are mixing contexts. You are
attempting to select a range on Sheet2 using cells that are on Sheet1.

Cheers,
Jason Lepack

 

[Bill Renaud]

As an aside, the line...

Set sheet1 = baseBook.Sheets(1)

....could be problematic, if Sheets(1) happens to be a chart sheet.
(Remember, "Sheets" is generic for all types of sheets in a workbook, i.e.
worksheet, chart, macro, etc.)

I think it is better to use something like the following, unless you
haven't bothered to set the tab names on your worksheets:

Set sheet1 = baseBook.Worksheets("YourWorksheetName")

Also, I generally use an object variable like "wsData" to refer to a
worksheet, rather than "sheet1". "sheet1" might be conflicting with the
CodeName assigned by Excel when the sheet was originally inserted into the
workbook (see the Project Explorer). This might cause problems (name
conflicts) after lots of extensive editing has been done and the symbol
tables have become large.

So, I would change your code to the following 2 declarations:

Dim wsDestination As Worksheet ' destination
Dim wsSource As Worksheet ' source