Solved

Saving Individual Worksheets Error

Posted on 2014-04-25
4
92 Views
Last Modified: 2014-05-02
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
Comment
Question by:mattfmiller
4 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40023876
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
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 100 total points
ID: 40025040
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 400 total points
ID: 40025238
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
 
LVL 1

Author Comment

by:mattfmiller
ID: 40037833
That's terrific.   Thank you very much.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

831 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