Copying multiple sheets to another workbook

[mwc0914]

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

[bernardoafs]

For you to go back to the original Workbook you just have to insert this command:

Windows ("Original File.xls").Activate

then you activate the sheet with the new data you have to copy as in the lines you wrote.

bernardoafs

[Dave Peterson]

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

[mwc0914]

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 statement look like then?

Thanks

[Dave Peterson]

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