[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

Saving Individual Worksheets Error

Hi I'm trying to put together a macro that saves individual sheets individual workbooks but I'm running into a failure to saveas error.  Help would be greatly appreciated. Thanks,

Sub AnotherAttempt()
     Application.DisplayAlerts = False
     
     Sheets("Branches").Select
     
Dim counter As Integer
Dim SheetNamer As String
Dim yourdate As String
 
yourdate = Format(Now(), "yyyy-mm-dd")
For counter = ActiveSheet.Index + 1 To Worksheets.Count
Worksheets(counter).Activate
SheetNamer = ActiveSheet.Name
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="H:\Test\" & SheetNamer & "|" & yourdate & ".xls"
ActiveWorkbook.Close
Next
     Application.DisplayAlerts = True
        End Sub

Open in new window

0
mattfmiller
Asked:
mattfmiller
2 Solutions
 
rspahitzCommented:
You didn't indicate what error you're getting, but if you're not starting on sheet1, this line will fail:

For counter = ActiveSheet.Index + 1 To Worksheets.Count

Open in new window


Maybe you want something like this:
For counter = ActiveSheet.Index + 1 To Worksheets.Count - ActiveSheet.Index

Open in new window

0
 
Ejgil HedegaardCommented:
You use the character | in the filename, and that is not allowed.
Others not allowed are \ / : * ? < >

To save all worksheets in the active workbook, avoid defining where to start, you could use this.

Sub SaveSheets()
    Application.DisplayAlerts = False
    
    Dim yourdate As String
    Dim ws As Worksheet
    
    yourdate = Format(Now(), "yyyy-mm-dd")
    
    For Each ws In Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:="H:\Test\" & ws.Name & "," & yourdate & ".xls"
        ActiveWorkbook.Close
    Next ws
    
    Application.DisplayAlerts = True
End Sub

Open in new window

0
 
FaustulusCommented:
I think it is better to deal with the alerts than to suppress them. In your task an alert would tell you that the sheet already exists, and my code would allow you a choice. Instead, I would plead for suppressing screen updating, assuming that you would rather get done faster than see the sheets flickering by.
Option Explicit

Sub AnotherAttempt()
    
    Const PathName As String = "H:\Test\"
    Dim Ws As Worksheet
    Dim n As Integer
    
    Application.ScreenUpdating = False
    For Each Ws In ActiveWorkbook.Worksheets
        If Ws.Name <> "Branches" Then
            Ws.Copy
            With ActiveWorkbook
                On Error Resume Next
                .SaveAs PathName & Ws.Name & _
                        Format(Date, " yyyy-mm-dd")
                If Err = 0 Then n = n + 1
                .Close False
            End With
        End If
    Next Ws
    Application.ScreenUpdating = True
    
    MsgBox n & " worksheet" & IIf(n = 1, " was", "s were") _
             & " saved.", vbInformation, "Task completed"
End Sub

Open in new window

The above code copies all sheets except "Branches". I figured that was your intention. However, the filter can be either removed or refined, as the case may be.

Please note that the vertical bar isn't permitted in a file name - at least on a PC it isn't.
Now returns a date/time stamp of which you suppress the time part by your format. I suggest to use Date instead. The vertical bar isn't permitted in PC file names. Therefore I have replaced it with a space which can be accommodated within the format mask.
BTW, consider placing the date at the beginning of the file name. That will cause the files you create to sort differently in Windows Explorer.
0
 
mattfmillerAuthor Commented:
That's terrific.   Thank you very much.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now