Copying multiple sheets to another workbook
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 |
Copying multiple sheets to another workbook
For you to go back to the original Workbook you just have to insert thi command: Windows ("Original File.xls").Activate then you activate the sheet with the new data you have to copy as i the lines you wrote. bernardoaf -- bernardoaf ----------------------------------------------------------------------- bernardoafs's Profile: http://www.excelforum.com/member.php...fo&userid=2899 View this thread: http://www.excelforum.com/showthread.php?threadid=48740 |
Copying multiple sheets to another workbook
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 |
Copying multiple sheets to another workbook
bernardoafs, How do I get control back to my destination workbook so I can paste the second time? I tried entering the following line: Windows(ThisWorkbook.Path & "\destination file.xls").Activate But I get a subscript out of range error... -- mwc0914 ------------------------------------------------------------------------ mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130 View this thread: http://www.excelforum.com/showthread...hreadid=487404 |
Copying multiple sheets to another workbook
Dave, What if I'm copying cells that contain a formula & I want to do a paste special of values only in the destination. What would my stement look like then? Thanks -- mwc0914 ------------------------------------------------------------------------ mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130 View this thread: http://www.excelforum.com/showthread...hreadid=487404 |
Copying multiple sheets to another workbook
This would change from:
origwkbk.worksheets("origin sheet a").range("b3:c198").copy _ destination:=newwkbk.worksheets("dest sheet A").range("b3") to: origwkbk.worksheets("origin sheet a").range("b3:c198").copy newwkbk.worksheets("dest sheet A").range("b3").pastespecial paste:=xlpastevalues Or you could just assign the values, too: dim rngtocopy as range dim destcell as range set rngtocopy = origwkbk.worksheets("origin sheet a").range("b3:c198") set destcell = newwkbk.worksheets("dest sheet A").range("b3") destcell.resize(rngtocopy.rows.count,rngtocopy.col umns.count).value _ = rngtocopy.value Using the range variables makes that last statement easier to type and makes the whole thing easier to change. mwc0914 wrote: Dave, What if I'm copying cells that contain a formula & I want to do a paste special of values only in the destination. What would my stement look like then? Thanks -- 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 |
All times are GMT +1. The time now is 11:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com