Hello,
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
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
Dim strSavePath As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False 'Don't show any screen movement
strSavePath = "H:\Finance\2013\Test\" 'This could be any folder you want. Make sure to add \ at the end.
Set wbSource = ActiveWorkbook
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
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
Is this possible?
Thanks for any help!
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
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
Dim strSavePath As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False 'Don't show any screen movement
strSavePath = "H:\Finance\2013\Test\" 'This could be any folder you want. Make sure to add \ at the end.
Set wbSource = ActiveWorkbook
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
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
Is this possible?
Thanks for any help!