Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple sheets to new Workbook | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
COPYING Workbook and sheets automatically | Excel Discussion (Misc queries) | |||
Copying Sheets to New Workbook | Excel Programming | |||
Copying Sheets to New Workbook | Excel Programming |