Reading/Writing Data In Other Sheets Without Selecting First

I've been having a bit of an issue with reading and writing data from other sheets.

Whilst I can read and write single cells fine using:

sheets(sheet Name).cells(x,y).value

using:

sheets(sheet Name).range(cells(x,y),cells(a,b)).value

does not appear to work.

Whilst this is easily worked around by simply selecting the sheet it does have a bit of an overhead and feels quite inelligent.

The main purpose I require this for is reading and writing ar开发者_运维技巧rays to inactive worksheets, if anyone has an alternative i'd be exceptionaly gratefull.

Cheers

If you want to avoid using Select or Activate could you not instead do?:

With Sheets("Sheet Name")

    arrData = .Range(.Cells(1,1), .Cells(2,1)).Value

End With

As far as I know this should work.

Dim ws As Worksheet

Set ws= Worksheets("MySheet")

       With ws
        .Range(ws.Cells(3, 1), ws.Cells(3, 14)).ClearContents
       End With

End If

This works really well without activating sheet

Using the cells method within a range object to refer to another worksheet requires you to activate that worksheet first I think (see section 5 in this from msdn)

Sub ReferToCells()
    Dim arrData() As Variant, i As Long
    Sheets("Sheet2").Activate
    arrData = Range(Cells(1, 1), Cells(2, 1)).Value

    For i = 1 To UBound(arrData)
        Debug.Print arrData(i, 1)
    Next i
End Sub

One small mistake: sheets(sheet Name).range(cells(x,y),cells(a,b)).value should be replaced by sheets(sheet Name).range(sheets(sheet Name).cells(x,y),sheets(sheet Name).cells(a,b)).value and it will work perfectly both for reading from and writing to other sheet of current book.

Reason: when you call Cells without referencing the target parent sheets, you are just calling the current active sheet's cells./