Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Saving Individual Worksheets Error

Posted on 2014-04-25
4
Medium Priority
?
97 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 23

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 400 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 1600 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

647 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