You could use some variables to represent each workbook:
Dim OrigWkbk as workbook
dim NewWkbk as workbook
set origwkbk = activeworkbook 'or thisworkbook????
set newwkbk = workbooks.open(ThisWorkbook.Path & "\destination file.xls")
'then don't use .select's.
origwkbk.worksheets("origin sheet a").range("b3:c198").copy _
destination:=newwkbk.worksheets("dest sheet A").range("b3")
'just the top left cell of the destination range is sufficient.
And repeat that as many times as you need.
mwc0914 wrote:
I want to copy some cells on a sheet to another workbook, then return to
my original workbook and on another sheet copy some cells there to
another sheet in the second workbook. I can accomplish this once, but
how do I get control back to my original workbook so I can go to the
second sheet and copy the cells? Then how do I make my destination
workbook active again so it can receive the paste the second time?
Below is what I have for the first copy/paste...
Sheets("origin sheet A").Select
Range("b3:c198").Select
Selection.Copy
Workbooks.Open (ThisWorkbook.Path & "\destination file.xls")
Sheets("dest sheet A").Select
Range("b3:c198").Select
ActiveSheet.Paste
--
mwc0914
------------------------------------------------------------------------
mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
View this thread: http://www.excelforum.com/showthread...hreadid=487404
--
Dave Peterson