Workbooks & worksheets

Hi Jill

I don't understand what you are trying to achieve.

A workbook already has multiple worksheets so to increase the number you just add new worksheets.

However if you want to create a new workbook from every worksheet in a workbook you can do the following:


Sub CreateNewWorkbooks()

Dim NewBook As Workbook
Dim OldBook As Workbook
Dim MySheet As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set OldBook = Workbooks(ActiveWorkbook.Name)


For Each MySheet In OldBook.Worksheets
If MySheet.Visible = True Then
MySheet.Copy
ActiveWorkbook.SaveAs Filename:=OldBook.Path & "\" & MySheet.Name , FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Hope this helps

Carlos