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.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.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




Reply With Quote