Thread: Copy Select Worksheets to User Defined Sheet?

I have workbook that has multiple sheets. When I push out a new version of this worksheet I want a user to be able to click a button to export all the sheets except "1" and "2" (for example) to the new workbook.

So...
1. the user clicks the button
2. All sheets are copied except the three I specify in the VBA
3. The screen comes up to allow the user to choose the workbook to export the sheets to

I haven't been able to find anything on moving multiple sheets like this on the internet, only methods to move a single sheet (with a static name).

What might this code look like? I really appreciate any help you can give me.

Could I make it simpler by just using that loop, saying move, and then have the normal excel "move or copy" box pop up for the user to select the open workbook to copy to? What might that code look like?

would it be xldialogmove?

Maybe it would be smart to use that, but put all the items in an array first? Otherwise won't that box show for every tab? It would be nice to add them to an array, then move them all at once. I'm not very familiar with putting sheets into an array and then calling it at all.

By the way, thanks so much for your help. I was really struggling with this.

No. But it is always the same sheets that won't be copied (that should help a lot.)

I've got this code below that selects the sheets I need, with your help. How can I now just show the excel move dialog and let the user move the selected sheets?

Dim ws As Worksheet

For Each ws In Sheets

    If ws.Visible And ws.Name <> "Instructions" And ws.Name <> "Chart Data" And ws.Name <> "Project-Chart" Then ws.Select (False)
Next

'Copy selected sheets to.... (user picks between the open workbooks, just like a normal excel sheet 'move.)

one last issue that I didn't predict. When it copies, it also adds the external references. Is there anyway I can edit this code so it will copy/move the sheets, keep the formulas, but not add the external references?

Thank you so much for your help so far. I did add to your reputation on the site btw :-)

Sub CopySheets()
Dim ws As Worksheet

For Each ws In Sheets

    If ws.Visible And ws.Name <> "Instructions" And ws.Name <> "var" And ws.Name <> "Chart Data" And ws.Name <> "Project-Chart" Then ws.Select (False)
Next
UserChoice = Application.Dialogs(xlDialogWorkbookMove).Show

End Sub

- I'd like to move them... if it is somehow easier to copy that would work too though.
- The reference is to the sheet location C:// I found a way to remove the link manually and change the source to the current file, but using the macro recorder I could not find a way to make this work under any sheet-name circumstance -- so I couldn't figure out the automation.