Solved

Saving Individual Worksheets Error

Posted on 2014-04-25
4
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 22

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
deleting filtered cell contents in excel 8 59
converting excel into labels 6 35
A WS within a WS 11 38
Excel + CountIfs + two colums 5 33
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

738 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