Avoid .activate

[Gordon Rainsford]

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

[Ken Johnson]

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]

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 <[email protected]> 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

[Ken Johnson]

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