Worksheets to new Workbooks

[Mark L]

I am wondering if anyone knows a quick way possibly even
through a macro that would allow me to move my worksheets
into seperate new workbooks. For instance my current
workbook has 20 sheets and I would like to move each one
of those sheets into an individual workbook. If anyone
knows any quick ways besides right clicking->move or copy-

 

[Ryan Poth]

Mark,

The following code will move all of the worksheets in the current workbook,
except the last remaining one, to new workbooks. Excel will not allow you to
move the last worksheet because the original workbook must contain at least
one worksheet.

Sub MoveSheets()
Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook
For Each ws In Worksheets
If Worksheets.Count > 1 Then
ws.Move
wb.Activate
End If
Next ws
End Sub


HTH,
Ryan

 

[Don]

Mark,

I'm sure there are others out there that can do a better
job on this coding but here's a way to create and save
each worksheet to a new workbook....it'll also keep your
original workbook intact. Be sure to set the variable "i"
to the proper settings for the number of worksheets you
have and adjust the save line to fit your file names. Also
make sure the first sheet selected in the code below
refers to the first (furthest to the left) sheet in your
workbook.



Sub Sheets_To_Books()

Dim i As Integer
Sheets("sheet1").Select
For i = 1 To 4
Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="C:\Book" & i
& ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
If i = 4 Then Exit For
ActiveSheet.Next.Select
Next i
Sheets("sheet1").Select
End Sub

HTH,

Don

 

[Ryan Poth]

Alternatively, a quick, non-VBA way to do this would be simply to drag and
drop each worksheet onto the Excel background. This will have the same effect
as right click->move (to new workbook).

 

[Arvi Laanemets]

Hi

Right-click on sheet's tab and select 'Move or copy';
Select 'New book' for destination;
Check 'Create a copy'
OK
Save the new workbook - Save As dialog is opened.

When you want to copy several sheets into same workbook, select wanted
sheets before (holding down Alt key, click on wanted tabs).

When you want to copy sheet(s) to some existing workbook, open it before.
Now you can select it as destination, and estimate the position of copied
sheets in destination workbook.

When copied sheets did contain links to other sheets, not copied with along,
convert ranges with such formulas to values before (Otherwise they will
refer to original workbook). When you want to preserve formulas in original
workbook, be sure you don't overwrite it accidently after that.

When your original workbook contained named ranges/functions, they are
copied along with. Delete abundant ones. WHen some names are needed in new
destination, check them and edit, when references are pointing to original
workbook - they don't work after the original workbook is closed.

 

[Gord Dibben]

Mark

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP