Solved

Excel VBA Save Sheets in Excel file as separate Excel Files

Posted on 2016-11-17
5
61 Views
Last Modified: 2016-11-17
Hi

I need to loop through all the sheets in my Excel file and if the sheet has a numeric name eg (1, 2 etc) then I want to save that sheet as a new separate file. I have the following code so far but am getting the error "Object Required" on the line      xWs.Copy

Sub oSave_Individual_Numeric_Sheets_As_Files()

Dim oDate As String
Dim oHole As String

On errot GoTo EH

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim i As Integer
Dim oSheetName As String
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Dim xWs As Worksheet


    For i = 1 To Sheets.Count
   
        For Each xWs In ThisWorkbook.Sheets
            oSheetName = xWs.Name
            If IsNumeric(oSheetName) = True Then
                 
                oDate = xWs.Range("D4").Value
                oHole = xWs.Range("D5").Value
                xWs.Copy 'Get Error here "Object Required"
                Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & oDate & " " & oHole & ".xls"
                Application.ActiveWorkbook.Close False
            End If
        Next
       
    Next i
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

Exit Sub
EH:
    MsgBox Err.Description & " eh55"
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
End Sub
0
Comment
Question by:murbro
5 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41891504
Remove the  For i = 1 To Sheets.Count loop.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891511
Try changing:

For Each xWs In ThisWorkbook.Sheets

to

For Each xWs In ActiveWorkbook.Sheets

Thanks
Rob
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41891523
And, the xWs.Copy is not specifying which sheet to copy, try:

Sheets(oSheetName).Copy

As MacroShadow says, remove the For I = 1 to Sheets.Count loop.  This is counting the number of sheets and then the routine below is going through all sheets that number of times. So if you have 10 sheets, it is copying each sheet 10 times and overwriting the previous copy each time.
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 41892456
You may encounter an error saying that the file type doesn't match the extension when you try to open a file saved by this macro. The cure for this is to specify the FileFormat property when you save the file. You didn't need to do this with Excel 2003 and earlier--but you do with Excel 2007 and later.

If your workbook contains a chart sheet with a numeric name, then that will cause a runtime error if you are looping through ActiveWorkbook.Sheets. The workaround is to loop through ActiveWorkbook.Worksheets instead.

I also removed the statements turning screen updating back on. These statements are not necessary, and will slow execution down (and cause a flicker), because Excel VBA does the same thing for you automatically when it finishes executing a macro.

Finally, I incorporated the changes regarding looping as suggested by MacroShadow & Rob Henson.
Sub oSave_Individual_Numeric_Sheets_As_Files()

Dim oDate As String
Dim oHole As String
Dim oSheetName As String
Dim xPath As String
Dim xWs As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error GoTo EH
xPath = ActiveWorkbook.Path

    For Each xWs In ActiveWorkbook.Worksheets
        oSheetName = xWs.Name
        If IsNumeric(oSheetName) = True Then
             
            oDate = xWs.Range("D4").Value
            oHole = xWs.Range("D5").Value
            xWs.Copy 'Get Error here "Object Required"
            ActiveWorkbook.SaveAs Filename:=xPath & "\" & oDate & " " & oHole & ".xls", FileFormat:=56
            ActiveWorkbook.Close False
        End If
    Next
   
    Application.DisplayAlerts = True

    Exit Sub

EH:
    MsgBox Err.Description & " eh55"
   
    Application.DisplayAlerts = True
   
End Sub

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 41892529
Thanks very much
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question