asked on

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

rspahitz

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

SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Faustulus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matt Miller

ASKER

That's terrific. Thank you very much.