Hi Everyone,
I've tried to troubleshoot this for two days now and I can't make heads or tales of it. I have a bunch of subroutines in Excel 2010 that I'm using to manipulate pivot tables, grab values, make reports and then export them out of the "engine" workbook into their own Excel 97 worksheets.
Everything is working great except for when I either call the CreateWorkbooks subroutine from the main code or when I try to execute it by just clicking on the macro button. It blows up my entire workbook, crashes and leaves me with no recourse to figure out why it's happening. From the little I can gather, it does appear to be happening at the step where the individual worksheet is copying, though I'm not 100% certain about it.
Here's the code with what I believe to be the offending line highlighted in red:
A few notes:
Any help on this would be awesome. Thanks so much for taking the time to read my post.
I've tried to troubleshoot this for two days now and I can't make heads or tales of it. I have a bunch of subroutines in Excel 2010 that I'm using to manipulate pivot tables, grab values, make reports and then export them out of the "engine" workbook into their own Excel 97 worksheets.
Everything is working great except for when I either call the CreateWorkbooks subroutine from the main code or when I try to execute it by just clicking on the macro button. It blows up my entire workbook, crashes and leaves me with no recourse to figure out why it's happening. From the little I can gather, it does appear to be happening at the step where the individual worksheet is copying, though I'm not 100% certain about it.
Here's the code with what I believe to be the offending line highlighted in red:
Rich (BB code):
Sub CreateWorkbooks_v3()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Worksheet
Dim strSavePath As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim strSavePathFull As String
strSavePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) 'Change this to suit your needs
Set wbSource = ActiveWorkbook
Application.DisplayAlerts = False
For Each sht In wbSource.Sheets
Select Case sht.Name
Case "WBK_Info", "VBA_Values", "Role_Picks", "PVT_Play", "Custom_Report", "Master_Pivot", "Template"
Case Else
sht.Copy
Set wbDest = ActiveWorkbook
With wbDest
If wbSource.Name = .Name Then
MsgBox "Your answer is NO in the security dialog"
' GoTo GoToNextSheet
End If
strSavePathFull = strSavePath & "\" & sht.Name & ".xls"
wbDest.SaveAs Filename:=strSavePathFull, FileFormat:=56
wbDest.Close 'Remove this if you don't want each book closed after saving.
End With
sht.Delete
End Select
Next
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End SubA few notes:
- I've used Select/Case and If/Then Statements to code for the exceptions. Neither have had an impact.
- When I take out both of those and just cycle through every worksheet in the workbook with no exceptions, I do not get the error.
- The error I am getting is not consistent: If I step through the code using F8, it almost never crashes. I've also had the code work successfully from a SubRoutine call from within my code and from just hitting the the "Select Macro" from the Developer Tab. But, both of those are when the program usually crashes and Excel Exits. That scenario fails sometimes if there is just one worksheet that is not an exception or if there are many. Also, the even weirder thing is that when there are many worksheets to copy, it will sometimes execute successfully for the first few and then it'll crash on like iteration 4 or 5.
Any help on this would be awesome. Thanks so much for taking the time to read my post.
Last edited:
