MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Moving (Selected) sheets to a new Workbook

Posted by J.Hardwicke on March 23, 2000 4:28 AM

Is there a way in selecting WorkSheets and moving them across to a new Workbook then be given the choice of filename and directory to save it in.

The snag is that the Workbook containing the Sheets can varry from amount of Sheets and Names of Tabs. However all of these can be moved except 2 static named Sheets e.g. "Main" & "Data"

Can someone out there please help. (Stuck with arrays etc).

Jeremy H.


Posted by Celia on March 23, 2000 5:35 AM

Unless of course what you want to do is part of a larger macro, is a macro really necessary?
It does not take long to perform the following steps :-

1.Save the file as a new filename and then delete the sheets "Main" and "Data" from the new file.
2.Open the original file and delete all sheets except "Main" and "Data"


Posted by J.Hardwicke on March 23, 2000 5:53 AM

Unfortunatly it is part of a process of Macros.
Is there a way to deal?


Posted by Celia on March 23, 2000 3:45 PM

The following should do it (but there is probably a simpler way) :-

Sub MoveWorksheets()
Dim originalWB As Workbook, sheet As Worksheet, response As Boolean
Set originalWB = ActiveWorkbook
'Copy all sheets to a new workbook
'Turn off alerts
Application.DisplayAlerts = False
'Delete sheets Data and Main from the new workbook
Sheets(Array("Data", "Main")).Delete
'Display SaveAs dialog box
response = Application.Dialogs(xlDialogSaveAs).Show
'Check that Cancel or Close was not selected
If response = False Then
MsgBox "You MUST save the new file." & Chr(13) & Chr(13) & "Run the macro again."
ActiveWorkbook.Close savechanges:=False
Exit Sub
End If
'Go back to original workbook
'Delete all sheets except Data and Main
For Each sheet In Worksheets
If sheet.Name <> "Data" Then
If sheet.Name <> "Main" Then
End If
End If
'Turn on alerts
Application.DisplayAlerts = True
End Sub


Posted by J.Hardwicke on March 24, 2000 1:19 AM

Celia, It worked like a dream.


You've got me out of the Rutt.