Copy worksheets to new workbook

Hi everyone,

I have the following question:

I have a workbook containing several worksheets I want to copy to a workbook of their own and save under a specific name/password through a macro.

However, I have five difficulties:
1) I want to new workbook to contain only 1 worksheet. So, only the worksheet copied from the original workbook, and no other worksheets.
2) I want to be able to manually select the destination file for the copied workbook, however. This should be the same file for all workbooks.
3) I want the data copied from original worksheet to be ‘pasted as value’ only, so no formulas in the new workbook.
4) I want the new workbook to be read-only. Therefore I want the workbook to be /locked and password protected. So, the password should be only for altering cells, not for opening the workbook. See also point below.
5) Regarding the naming and passwording part I have made a matrix table in a worksheet named: “Rekeningen” (this worksheet is in the original workbook). This table contains three columns:

Column A, cells 40:65, contains the names of the worksheets I want to copy
Column B, cells 40:65, contains the names of the new workbooks
Column C, cells 40:65, contains the passwords of the new workbooks

So for example
Column______A_____________B________________C_______
________Worksheet__Workbookname_____Password_____
Row 40_____ABC________Pit200___________B43AA_____
Row 41_____EFD________egs210___________BVF#2_____

I want worksheet ABC, to be copied as value to a seperate workbook, containing only 1 worksheet. The new workbook should be named Pit200 and locked and password protected under the password “B43AA”

Sorry, for my long question, but I hope you can help me out here!

Thanks!
Regards,
Edward

Hi, Thanks but, for the row

Please Login or Register  to view this content.

I get the error: "end with without with"

Please help me.

Kind regards,

Edward

Take out the "End With" above the "next R" line.

Thanks, but when I do that, all that happens is that I can choose a destination directory after which excel closes down and nothing else happens. What am I doing wrong?

Regards,
Edward

Code works for me just fine. I just ran a practice run with a modified sheet of my own. Is there a way you can give us a copy of the spreadsheet?

Hi, Thanks!

I tried the code in another workbook and there it worked fine. Attached is a (stripped) version of my workbook, in which the macro doesn't work.

Note that the macro is in module 3, and I have made a button in sheet 'control', there the button is called 'export data'. When I press that button, all that happens is that I get to select a destination file and my workbook is saved and closed. INstead of copying selected sheets to destination file.

Please help me!

Thanks!

Kind regards,

Edward

Bump no response

I got your PM. I took a look at the sheet, I fixed the issue for you.

Please Login or Register  to view this content.

The macro was originally trying to pull the sheets, workbook name, and password from the wrong worksheet. This is why the macro never worked on that particular file.

Thanks! It works great now!

I do have one question though, is it possible to copy/paste everything as value? (or at least cells B7 and E7)?

Thanks!