Results 1 to 3 of 3

Thread: RESOLVED: HOW TO: Paste to a different sheet PROGRAMATICALLY ???

Threaded View

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved RESOLVED: HOW TO: Paste to a different sheet PROGRAMATICALLY ???

    Esteemed Forum Participants and Lurkers:
    ===============================
    EXCEL

    I am trying to copy a range of cells from one sheet to a location on another sheet (actually one out of a set of 24 sheets) ... all PROGRAMATICALLY! I can get the 'Paste' to work fine with an explicit reference string, but I can't seem to make it work with variables. I don't want to have to make the extra step of seleting/activating the page because I have a LOT of copies to do in a big loop.
    Code:
    Sub Macro1()
    Dim asheet As Worksheet
    Dim bsheet As Worksheet
    
    Set asheet = Sheets("Sheet1")
    Set bsheet = Sheets("Sheet2") ' Yes, there are 3 sheets in the book
    
    asheet.Range(asheet.Cells(5, 1), asheet.Cells(6, 2)).Copy
    'This works PERFECTLY!
    ActiveSheet.Paste Destination:=Range("Sheet3!$B$3")
    
    asheet.Range(asheet.Cells(5, 1), asheet.Cells(6, 2)).Copy
    
    'NONE of the following work!
    'ActiveSheet.Paste Destination:=bsheet.Range(bsheet.Cells(3, 2))
    'ActiveSheet.Paste Destination:=bsheet.Range(Cells(3, 2))
    'ActiveSheet.Paste Destination:=Range(bsheet.Cells(3, 2))
    
    End Sub
    Does anyone have any ideas about how to do this? Do I just have to 'bite the bullet' and 'Select' the page? Thank you for your gracious comments, suggestions and assistance.
    Last edited by Webtest; Mar 15th, 2005 at 09:13 AM. Reason: Question Resolved
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width