I have a user that loves to keep a single workbook with numerous worksheets for each week. This worksheet hold information for about 2 years of data. Each worksheet is named mm-dd-yy with the date being the first day of the week. I need to create a macro that takes the past worksheets and saves them to a different file with the file name being the mm-dd-yy of the worksheet that it contains.
Any help or advice would be greatly appreciated.
Thanks
Last edited by VBA Noob; 11-06-2008 at 02:04 PM.
I think I may have found a solution.
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.
ActiveSheet.Delete
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks