Solved: Range method failed

[chaynes]

Hi I am running this in Excel 2003 and I get a range method failed error. I am trying to transfer the values from wsNewSales to a sheet called Cash Sales in wbNew (avoiding copy and paste values) on a given range. (I was just using .Cells.Value = .Cells.Value but was hoping this method would speed things up?)
Any help greatly appreciated.
Craig



Dim lastRow As Integer
Dim lastCol As Integer
With wbNew
With wsNewSales.Range("F1")
lastRow = .End(xlDown).Offset(4, 0).Row
lastCol = .End(xlToRight).Offset(0, 2).Column
End With
.Worksheets("Cash Sales").Range(Cells(1, 1), Cells(lastRow, lastCol)).Value = wsNewSales.Range(Cells(1, 1), Cells(lastRow, lastCol)).Value
End With

[Bob Phillips]

Not tested it, but try this



Dim lastRow As Integer
Dim lastCol As Integer
With wsNewSales.Range("F1")
lastRow = .End(xlDown).Offset(4, 0).Row
lastCol = .End(xlToRight).Offset(0, 2).Column
End With
With wbNew
.Worksheets("Cash Sales").Range(.Cells(1, 1), .Cells(lastRow, lastCol)).Value = _
wsNewSales.Range(wsNewSales.Cells(1, 1), wsNewSales.Cells(lastRow, lastCol)).Value
End With

[chaynes]

Thanks xld but that didn't work either

[Norie]

And how did it not work?

And what do you actually want to do with the code?

[chaynes]

The error is: "Object Doesn't support this property or method".

I am trying to transfer the values in the range (1,1) to (lastRow,lastCol) from wsNewSales to a sheet called "Cash Sales" in wbNew.

Thanks

[Bob Phillips]

Dim lastRow As Integer
Dim lastCol As Integer
With wsNewSales.Range("F1")
lastRow = .End(xlDown).Offset(4, 0).Row
lastCol = .End(xlToRight).Offset(0, 2).Column
End With
With wbNew.Worksheets("Cash Sales")
.Range(.Cells(1, 1), .Cells(lastRow, lastCol)).Value = _
wsNewSales.Range(wsNewSales.Cells(1, 1), wsNewSales.Cells(lastRow, lastCol)).Value
End With

[chaynes]

Ah, I see, because it's within the With wbNew block you need to include wsNewSales each time you refer to the cells.
Brilliant, thank you very much xld :thumb

[Bob Phillips]

Yes, you need the whole object hierarchy, otherwise it will default, and the default will likely be a different sheet.