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

[Webtest]

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.

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.

[Ecniv]

Sub Macro1()
    Dim asheet As Worksheet
    Dim bsheet As Worksheet
    Dim rng as Range

    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!

    set rng = bsheet.range("$B$3")
    rng.paste
'    bSheet.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

Don't use active sheet - as it points to the active sheet. (duuh??)
Use a range object to set the starting position of the paste.

See if the above works then expand it to do what you need it to.
Any problems, please post again and I'll try a real example for you ok?

[Webtest]

Vince:

I get an error message at "rng.Paste":
"Object doesn't support this property or method"
However, "rng.Select" does not result in an error (but it doesn't paste either!).

Actually, I did get "ActiveSheet.Paste" to work last night ... after numerous guesses, I finally discovered that if I set the ENTIRE Paste range accurately, it works with variables. Thanks for your commented out suggestion "bsheet.Paste" ... it works great!

Option Explicit
Sub Macro1()
'
Dim asheet As Worksheet 'First Sheet in book
Dim bsheet As Worksheet 'Second Sheet in book
Dim rng As Range        'Misc Range variable
Dim cb As Integer       'Beginning Column
Dim ce As Integer       'Ending Column
Dim rb As Integer       'Beginning Row
Dim re As Integer       'Ending Row

Set asheet = Sheets("Sheet1")
Set bsheet = Sheets("Sheet2")
' Define the Source Area for the Copy/Paste
cb = 3  'left column of range
ce = 6  'right column of range
rb = 2  'top row of range
re = 3  'bottom row of range
' Copy the Source Area
Range(asheet.Cells(rb, cb), asheet.Cells(re, ce)).Copy
' Define the Target Area Cells
cb = cb + 2: ce = ce + 2
rb = rb + 3: re = re + 3
' Define the Target area Sheet and Cells
' To use variables, you MUST define the ENTIRE paste area!!
'   ... not just the Top-Left cell as with explicit reference strings
Set rng = Range(bsheet.Cells(rb, cb), bsheet.Cells(re, ce))
'rng.Paste  '<< This does NOT work
bsheet.Paste Destination:=rng  'This works!

End Sub

Thanks for your input ... if you get 'rng.Paste' to work somehow, I would still like to know about it, but I think it has to reference a sheet object instead of a range object.