how to copy/paste to new workbook several times....

**sunilmulay** · 06-15-2009, 06:39 AM

Hi there

I'm trying to create an "export" feature where certain data can be copied out to a new workbook.

I want to copy out the following from the current workbook to a new one:
Sheets("1TR").Range("C33:M999") to range("A1") in the new workbook
and
Sheets("1PL").Range("K33:K999") to range ("L1") in the new workbook.

The problem I'm having is that it is not just one copy/paste exercise, but two, and I don't know how to reference the new workbook as it doesn't have a filename....

Please see below sample code. Any idea how to carry out this procedure?

Thanks
Sunil

Please Login or Register  to view this content.

**Charlize** · 06-15-2009, 08:22 AM

Don't promise a thing, but you could try this. Use F8 when stepping through the coding to see what action is doing what. When you use excel 2007, you'll have to create extra references to the worksheets and the ranges (as I did for the workbooks).

Please Login or Register  to view this content.

Charlize

**broro183** · 06-15-2009, 08:26 AM

hi Sunil,

What happens when you step through the code line by line using the [F8] key?
Do you get any error messages when the code stops, if so, what are they?

I believe your error is 2 lines further down in the code where you state:

Please Login or Register  to view this content.

Here are some ideas for using object variables (edit: on posting, I see Charlize beat me to it

) rather than strings for identifying workbooks & also an alternative approach to paste special:
(note the code is untested)

Please Login or Register  to view this content.

hth
Rob

**sunilmulay** · 06-16-2009, 08:22 AM

Hi there...

thanks very much for your replies.

I went with your suggestions, and it works a treat. I'm having a problem now though with a different part of my "export" procedure...

Please see attached sample workbook. if you run the Export Macro, see what happens.

The new file created by the macro should have no blank rows....the following part of the code is deleting SOME of the blank rows but not all....any idea what is going on? I'm totally baffled!

Thanks in advance!!

Sunil

Please Login or Register  to view this content.

**broro183** · 06-16-2009, 10:51 AM

hi Sunil,

Thanks for the feedback, as the original of this post is solved, can you please mark the thread as solved and then start your new question in a new thread?

Rob