copy range from active worksheet to another workbook

**omarq** · 01-26-2011, 06:03 AM

Hi, I want to copy data from a range (A:B) in my activeworksheet to another defined workbook onto a worksheet with the same name. I also want to open the file I am pasting into at the same time. Is there any code around that already does something like this?

Thanks in advance

**JBeaucaire** · 01-26-2011, 06:40 AM

To set a variable to the active workbook and activesheet:

Please Login or Register  to view this content.

Once that is set, you can refer to that sheet by variable name from anywhere.

To open a predefined workbook, then set a specific sheet in that workbook as the target, it's pretty similar:

Please Login or Register  to view this content.

Now that you have all the variables in place, you can copy directly, then close the workbook you opened, saving it:

Please Login or Register  to view this content.

**omarq** · 01-26-2011, 07:20 AM

Thanks - Thats great.

As an enhancement is it possible to do the same with a workbook, if the sheets in each are named the same?

**JBeaucaire** · 01-26-2011, 07:27 AM

The same as what? Questions like that should be accompanied by clear examples.

**omarq** · 01-26-2011, 07:32 AM

Sorry,

So for example... if I have a workbook with 3 worksheets named Sheet1, Sheet2 and Sheet3. And I want to paste the Range A:B of each of these respective sheets into another destination workbook which has worksheets also named Sheet1, Sheet2 and Sheet3. Would I be able to modify this to do that? So instead of the active worksheet, it would be the active workbook?

**JBeaucaire** · 01-26-2011, 08:01 PM

Same ideas, but we leave the destination open:

Please Login or Register  to view this content.

**omarq** · 01-27-2011, 09:41 AM

I seem to get an error on this line

Please Login or Register  to view this content.

Run-time error "424"

**JBeaucaire** · 01-27-2011, 10:10 AM

My bad:

Please Login or Register  to view this content.

**omarq** · 01-27-2011, 10:38 AM

Thanks - the copy is working now. Just the book2 is closing once complete. how can I leave it open?

**JBeaucaire** · 01-27-2011, 10:57 AM

Take out the line of code that is closing the workbook.

**omarq** · 02-03-2011, 09:04 AM

Thanks for your feedback here this is really great help. :-)

I have a couple of follow up questions on this macro. If I want to change it to copy from "sheet1" in the active workbook and paste in "sheet2" in the destination workbook... is there a simple way to do this?

**JBeaucaire** · 02-03-2011, 11:51 AM

Please Login or Register  to view this content.

**omarq** · 02-03-2011, 06:21 PM

thanks!

if I want to select a range manually (by highlighting; therefore would be different every time) and send those cells to the destination (below) how should I write that?

Please Login or Register  to view this content.

(p.s. tell me if this is too many questions)

copy range from active worksheet to another workbook

LinkBack

Thread Tools

Rate This Thread

Display

copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Re: copy range from active worksheet to another workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions