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

--
Gordon Rainsford

London UK

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

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


Ken Johnson wrote:

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

London UK

Hi Gordon,
tiny little things aren't they.
Ken Johnson