Solved

Saving Individual Worksheets Error

Posted on 2014-04-25
4
91 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro 6 49
DBF to ... Converter 5 38
Excel VBA - merge "specific" data from workbook in to "new" workbook. 19 22
Dynamic Filter ? 4 12
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now