Can you automatically save each sheet as a seperate file?

[imperium1980]

I have around 400 spreadsheets each containg half a dozen sheets. I need to save each sheet as a seperate file and then I will use SAS to pull data from each of them. I can't see a way to do this in Excel other than manually. Can it be done with a macro?

Any guidance much apppreciated.

 

[jbeaucaire]

You want ONE macro to open 400 workbooks stored in one folder...save out each sheet in each workbook with a different unique name? That's not as hard as it sounds.

What would you like the saved sheets to be called? We can sequence them with any name you wish. We need to select something that won't allow duplicate names to occur, so probably best not to base the new workbook names on the individual sheetnames, if that's ok.

If you provide:

1) Path name to the folder holding the 400 workbooks

2) Path name to the folder where you want the new workbooks saved to, it should be different from the path above just to insure there are no conflicts.

3) The naming convention you want to use for the new workbooks.

 

[imperium1980]

You want ONE macro to open 400 workbooks stored in one folder...save out each sheet in each workbook with a different unique name? That's not as hard as it sounds.

What would you like the saved sheets to be called? We can sequence them with any name you wish. We need to select something that won't allow duplicate names to occur, so probably best not to base the new workbook names on the individual sheetnames, if that's ok.

If you provide:

1) Path name to the folder holding the 400 workbooks S:\Production\Public\Foreign Matter Log

2) Path name to the folder where you want the new workbooks saved to, it should be different from the path above just to insure there are no conflicts. S:\Production\Public\Foreign Matter Log\Grade Inspections\

3) The naming convention you want to use for the new workbooks. Something like "Name_of_Worksheet_0001" then just number them sequentially. This part doesn't really matter as SAS just uses a llist of filenames and opens each sheet one by one, takes what it wants then closes it again.[/QUOTE]

Much appreciated.

 

[jbeaucaire]

Here you go...

Option Explicit

Sub SaveAllSheetsFromWBsInFolder()
'JBeaucaire  (11/4/2009)
'Open all .XLS and save each sheet separately with unique name
Dim fName As String, fPath As String, fPathOut As String
Dim wbkOld As Workbook, ws As Worksheet, Cntr As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

fPath = "S:\Production\Public\Foreign Matter Log\"
fPathOut = "S:\Production\Public\Foreign Matter Log\Grade Inspections\"
fName = Dir(fPath & "*.xls")
Cntr = 1

    Do While Len(fName) > 0
        Set wbkOld = Workbooks.Open(fPath & fName)
        For Each ws In Worksheets
            If ws.Index < Sheets.Count Then ws.Move
            ActiveWorkbook.SaveAs fPathOut & ActiveSheet.Name & "-" & Format(Cntr, "0000"), FileFormat:=xlNormal
            ActiveWorkbook.Close
            Cntr = Cntr + 1
        Next ws
        fName = Dir
    Loop
    
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[imperium1980]

Thank you so much, this will save me countless hours. Much appreciated! This would have taken me weeks to work out

Cheers

 

[smerrick]

This is surely my candidate for 'Macro of the year' if ever such an award existed!

Very useful indeed.