Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Avoid .activate

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gordon Rainsford - 25 Jan 2006 14:32 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.