I have a piece of code as below that works fine, but I can't find an
alternative that works, avoiding the two .Activate lines .
I've tried with...end with, but it gives an error.
Any suggestions?
Dim bottomRow As Long
bottomRow = Sheets("Scores").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Results").Activate
Sheets("Results").Range(Cells(3, 1), Cells(bottomRow,6)).Copy
Sheets("Ladder").Range("b3").PasteSpecial Paste:=xlValues
Sheets("Ladder").Activate
Sheets("Ladder").Range(Cells(3, 2), Cells(bottomRow, 7)) _
.Sort Key1:=Range("g3"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom

Signature
Gordon Rainsford
London UK
Ken Johnson - 25 Jan 2006 15:15 GMT
Hi Gordon,
I've removed the .Activates and used With/End Withs to address the
Ranges and Cells and tested it on dummy Scores, Results and Ladder
sheets. The Ladder sheet ended up with values pasted in it starting at
B3 and extending across to column G (without any errors messages), so
hopefully this is the solution.
Dim bottomRow As Long
bottomRow = Sheets("Scores").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Results")
.Range(.Cells(3, 1), .Cells(bottomRow, 6)).Copy
End With
Sheets("Ladder").Range("b3").PasteSpecial Paste:=xlValues
With Sheets("Ladder")
.Range(.Cells(3, 2), .Cells(bottomRow, 7)) _
.Sort Key1:=.Range("g3"), Order1:=xlDescending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Ken Johnson
Gordon Rainsford - 25 Jan 2006 15:31 GMT
Thanks Ken,
I see what I was doing wrong now: I didn't put the period before the
Cells as well as before the Range.
Gordon
> Hi Gordon,
> I've removed the .Activates and used With/End Withs to address the
[quoted text clipped - 20 lines]
>
> Ken Johnson

Signature
Gordon Rainsford
London UK
Ken Johnson - 25 Jan 2006 15:58 GMT
Hi Gordon,
tiny little things aren't they.
Ken Johnson