MACRO to save worksheets as seperate files ?

Posted by Steven on November 13, 2001 9:53 PM

Hi all,

I would like to be able to run a macro which saves the various worksheets in a workbook under their respective names in seperate files.

To give a bit more background information: I have a file which contains many worksheets. Each worksheet contains personal information and is named after that person. In order to send the person the confidential information, I need to strip the workbook from all the other sheets and name the workbook (with one sheet left) after that person. Without a macro I have to do this many times (unfortunately !).

So my question is whether anybody knows about a macro that is able to split up a workbook with x sheets in x files and giving that file the name of that particular worksheet ?

Thanks in advance for your feedback.

Steven

Posted by Dank on November 14, 2001 12:51 AM

Hello,

How about this?

Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Temp\" 'Change this to suit your needs

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next

Application.ScreenUpdating = True

Exit Sub

ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

Hope it helps,
Daniel.

Posted by Steven on November 14, 2001 2:42 PM

Hi Daniel,

Fantastic ! This is exactly what I need and it will save me lots of time. I am again amazed by what macro's can do and this stimulates me to learn how to write these codes myself (still a long way to go though ...)

Many thanks.

Steven