Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
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.
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