excel computer course london - Save Sheets As Indevidual Files

Resolved · Low Priority · Version Standard

Mon 22 Oct 2007, 16:33 Delegate David said...

David has attended:
Excel VBA Intro Intermediate course

Save Sheets As Indevidual Files

What code should I use If I want to save each sheet in a workbook as an individual File.

Cheers!

Wed 24 Oct 2007, 11:03 Trainer Carlos said...

RE: Save Sheets As Indevidual Files

David

Could you clarify this further.

Do you want save the worksheet as a separate New Workbook or just save the worksheet within the workbook without saving the rest of the workbook

Carlos

Wed 24 Oct 2007, 11:45 Trainer Carlos said...

RE: Save Sheets As Indevidual Files

David

Further to the above you cannot save a worksheet separately within a workbook. You need to save the whole workbook.

To separate sheets within a workbook into separate individual workbooks do the following code:

Sub SaveSheetsSeparately()

Dim NoSheets As Integer
Dim i As Integer

NoSheets = Sheets.Count

For i = 1 To NoSheets
Sheets(i).Activate
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\" & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close
Next i

End Sub

In the code the ActiveSheet.copy command is not like copy and paste.
It creates a copy of the active sheet, in a new workbook.
The new workbook is then saved, thus saving all the sheets as new separate workbooks.

You can adapt the code to save just one sheet by removing the For loop.

Hope this helps

Carlos

Thu 25 Oct 2007, 11:27 Delegate David said...

RE: Save Sheets As Indevidual Files

Thanks Carlos,

From you original code I ended up with,

Sub SaveSheetsSeparately()
Application.ScreenUpdating = False

Dim i As Long
Dim FName As Variant
Dim varOpenFile As Variant
Dim Response As Byte

On Error GoTo EH

MsgBox "Please open the workbook you wish to save the sheets from."

varOpenFile = Application.GetOpenFilename
Workbooks.Open Filename:=varOpenFile, _
UpdateLinks:=0

With ActiveWorkbook
For i = 1 To Worksheets.Count
If .Worksheets(i).Visible = xlSheetVisible Then
Sheets(i).Activate
ActiveSheet.Copy
Response = MsgBox("Do you want to save " & ActiveSheet.Name & "?" _
, 292, "Totals")

If Response = vbNo Then
ActiveWorkbook.Close savechanges:=False
Else
FName = Application.GetSaveAsFilename(InitialFileName:=ActiveSheet.Name, _
filefilter:="Excel Files (*.xls), *.xls", _
Title:="SaveAs")

If FName = False Then
MsgBox ActiveSheet.Name & " will not be saved because you pressed Cancel." _
& vbCrLf & "You will now move onto the next sheet."
ActiveWorkbook.Close savechanges:=False
Else
ActiveWorkbook.Saveas Filename:=FName
ActiveWorkbook.Close
End If
End If
End If
Next i

End With
ActiveWorkbook.Close savechanges:=False
MsgBox "Your Report is Split. Congrats!" _
& vbCrLf & "Now go thank Dave Pilbeam for saving 10 minutes of your life! "

Exit Sub

EH:

If Err.Number = 1004 Then
MsgBox "You didn't choose a report. This application will now close, and you can then try again."
Else
MsgBox "Whoo Nelly!!! Don't know what's gone wrong but this might help... Type: " _
& Err.Number & vbCrLf & Err.Description

End If

End Sub

I needed it to ignore hiden sheets and ask where you wanted to save them, plus deal with the errors. Hopefully I got them all.

Thanks agian Carlos, I think this training will make a real difference in my work.