Copy data from workbook, create new workbook, paste data to new worbook?

**mpkavanagh** · 01-27-2011, 01:56 PM

I am using a UserForm to run a sorting macro on whatever data is entered into the UserForm text box. I then want to create a new workbook (name is time and date), copy data (Columns A:G) from the original workbook and paste to the new workbook, then save and close the new workbook. The UserForm will then clear the text box and you can add more data (which will go into another new workbook).

The only problem I am having is pasting the data between the two. Any ideas? I have made a comment in the code below where I think I need to add something.

Thanks again!

Please Login or Register  to view this content.

**stnkynts** · 01-27-2011, 04:12 PM

It appears to me that you already have your columns copied. See

Please Login or Register  to view this content.

btw you can just write this as Columns("A:G").Copy

Not sure which workbook is the active one and/or which one you want to paste to but in the spot you indicated you could do something like:

Please Login or Register  to view this content.

If you need to paste it in the new workbook but its not you could say

Please Login or Register  to view this content.

Let me know if this works and makes sense for you.

**mpkavanagh** · 02-04-2011, 12:55 PM

Thanks! Sorry about the delay in getting back here.

I have tried both pieces of code you provided, but I am getting the following error:

Run-time error '1004':

PasteSpecial method of Range class failed

And the Debug command in VBA highlights the following piece of code:

Please Login or Register  to view this content.

This the same issue I was having before when I was trying to paste to the new workbook.

Thoughts?

**mpkavanagh** · 02-14-2011, 12:06 PM

BUMP. Still having some issues (please see my last post).

**mpkavanagh** · 02-16-2011, 05:10 PM

BUMP. Still looking.

**watersev** · 02-16-2011, 05:23 PM

do you have any other data on the sheet with the range to copy? Maybe you will go for this option:

Please Login or Register  to view this content.

This will move copy of the activesheet to a new workbook.

**mpkavanagh** · 02-16-2011, 06:06 PM

I added the copy code but do I need to pair it with a paste command after I create the new workbook?

Please Login or Register  to view this content.

**watersev** · 02-16-2011, 06:27 PM

it might be like this

Please Login or Register  to view this content.

**mpkavanagh** · 02-16-2011, 06:39 PM

Thank you! It works great.

One more side note, it seems that this code creates and saves a new workbook (which is what I want it to do), but it also opens a new Workbook every time (Book1, Book2, Book3, etc.) I run it. Is there any reason for this in my code?

EDIT: I still can't access old posts to change it to SOLVED. Can I mode change this to SOLVED?

EDITx2: I found the problem, ActiveSheet.Copy appeared twice in my code. *sigh* This one is done, thanks!