VBA Split Macro for Specific Sheet Names is being weird as heck!

[cutemeatball]

So I use this macro below to split my workbook into different workbooks depending on sheet names. However, only my last one SplitDAS does not work whenever I use SplitTechnologies Macro (the first one that runs the other 4 macros). I checked sheet names 100 times. And I found out that if I just run the SplitDAS macro (the last one) separately from SplitTechnologies Macro, it will work. Anyone know why? The error is a subscript out of range 9 window that highlights my first VBA code line in SplitDAS.

Any help or measures appreciated!

Thanks!

Sub SplitTechnologies()
'change macro names to suit


Call SplitButch
Call SplitAV
Call SplitDAS
Call SplitSEC

End Sub

Sub SplitButch()
'
' SplitSheets Macro
'

'
    Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Select
    Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e237915\Desktop\Security AV DAS ALL\SEC AV DAS.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        
End Sub

Sub SplitAV()
'
' SplitSheets Macro
'

'
    Sheets(Array("AV", "AV RSS", "AV Divison")).Select
    Sheets(Array("AV", "AV RSS", "AV Divison")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e237915\Desktop\Security AV DAS ALL\AV.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
End Sub


Sub SplitSEC()
'
' SplitSheets Macro
'

'
    Sheets(Array("Security", "Security RSS")).Select
    Sheets(Array("Security", "Security RSS")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e237915\Desktop\Security AV DAS ALL\SEC.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Sub SplitDAS()
'
' SplitSheets Macro
'

'
Sheets(Array("Wireless", "Burkhart", "Barnhill", "Allen", "Finger", "McCallum", "Wireless Division")).Select
Sheets(Array("Wireless", "Burkhart", "Barnhill", "Allen", "Finger", "McCallum", "Wireless Division")).Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\e237915\Desktop\Security AV DAS ALL\DAS.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

 

[Zot]

I think this is the common problem. During execution, active workbook change whenever you open or save workbook. Since the macro only refers to sheet name but no workbook reference, it will look for the sheet name in whichever active workbook at that time.

During SplitBunch, you select sheets Security, AV, Wireless, SEC & AV, Security RSS, AV RSS, SEC & AV RSS, AV Divison as save it as a new workbook. Now, new active workbook is this newly created workbook.

You run SplitAV and it is selecting sheets AV, AV RSS, AV Divison from new active workbook. No problem because all those sheet are in the new workbook. NOw active workbook switched to this newly created workbook that contains sheets AV, AV RSS, AV Divison only.

When macro try to run SplitDAS, it was trying to select sheets Wireless, Burkhart, Barnhill, Allen, Finger, McCallum, Wireless Division which cannot be found. That caused the error.

Avoid using Activate if possible. Defining variables make it easy to write code. Example

Dim ws1 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")    ' Change accordingly. Just need to use ws1 to refer to this specific sheet in macro if you need it.

'Instead of
wb.Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Select
    Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e237915\Desktop\Security AV DAS ALL\SEC AV DAS.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        
'use
wb.Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Select
    wb.Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", _
        "SEC & AV RSS", "AV Divison", "Wireless Division")).Copy
    wb.SaveAs Filename:= _
        "C:\Users\e237915\Desktop\Security AV DAS ALL\SEC AV DAS.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

This way the macro will refer to the original workbook you work on. No need to reactivate