Splitting specific worksheets and saving them into new workbooks!

[cutemeatball]

Hello,

So I tried to make my own macro below and it doesn't seem to work. Gives me the error 9 message. Can anyone assist? Basically I am trying to split specific worksheets from a workbook and save them as a new workbooks (multiple workbooks).

Sub SplitTechnologies()

Call SheetstoBooksButch
Call SheetstoBooksSecurity
Call SheetstoBooksAV
Call SheetstoBooksDAS

End Sub



Sub SheetstoBooksButch()



ThisWorkbook.Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", "SEC & AV RSS", "AV Division", "Wireless Division")).Copy
ActiveWorkbook.SaveAs "SEC AV DAS"




End Sub

Sub SheetstoBooksSecurity()


ThisWorkbook.Sheets(Array("Security", "Security RSS")).Copy
ActiveWorkbook.SaveAs "SEC"




End Sub

Sub SheetstoBooksAV()


ThisWorkbook.Sheets(Array("AV", "AV RSS", "AV Division")).Copy
ActiveWorkbook.SaveAs "AV"




End Sub

Sub SheetstoBooksDAS()


ThisWorkbook.Sheets(Array("Wireless", "Wireless Division", "Burkhart", "Barnhill", "Allen", "Finger", "McCallum")).Copy
ActiveWorkbook.SaveAs "AV"




End Sub

 

[John_w]

The macro recorder is useful for this. It generated:

Sub Macro1()
    Sheets(Array("AV", "AV RSS", "AV Division")).Select
    Sheets("AV RSS").Activate
    Sheets(Array("AV", "AV RSS", "AV Division")).Copy
    ChDir "C:\Temp"
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

which can be refined by removing unnecessary lines, closing the new workbook and unselecting the multiple sheets:

Sub Macro1()
    Sheets(Array("AV", "AV RSS", "AV Division")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("AV").Select
End Sub

 

[cutemeatball]

Hi,

So I tried your refined macro and it gave me a error 9 and it took me to the first line Sheets(Array*****

Can you advise? Thanks!

Sub ALLsplits()

Call Butchworkbook
Call SECworkbook
Call AVworkbook
Call DASworkbook

End Sub



Sub Butchworkbook()


    Sheets(Array("Security", "AV", "Wireless", "SEC & AV", "Security RSS", "AV RSS", "SEC & AV RSS", "AV Division", "Wireless Division")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("SEC AV DAS").Select


End Sub

Sub SECworkbook()


    Sheets(Array("Security", "Security RSS")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("SEC").Select


End Sub

Sub AVworkbook()

    Sheets(Array("AV", "AV RSS", "AV Division")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("AV").Select
    
End Sub

Sub DASworkbook()



    Sheets(Array("Wireless", "Wireless Division", "Burkhart", "Barnhill", "Allen", "Finger", "McCallum")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("DAS").Select





End Sub

The macro recorder is useful for this. It generated:

Sub Macro1()
    Sheets(Array("AV", "AV RSS", "AV Division")).Select
    Sheets("AV RSS").Activate
    Sheets(Array("AV", "AV RSS", "AV Division")).Copy
    ChDir "C:\Temp"
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

which can be refined by removing unnecessary lines, closing the new workbook and unselecting the multiple sheets:

Sub Macro1()
    Sheets(Array("AV", "AV RSS", "AV Division")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\AV.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("AV").Select
End Sub
[/CODE
[/QUOTE]

 

[John_w]

So I tried your refined macro and it gave me a error 9 and it took me to the first line Sheets(Array*****

In which macro? Are the sheet names correct?

All the macros are saving as "C:\Temp\AV.xlsx"

 

[cutemeatball]

In which macro? Are the sheet names correct?

All the macros are saving as "C:\Temp\AV.xlsx"

The one with Security in it (very first line)



These are the sheet names:

 

[cutemeatball]

In which macro? Are the sheet names correct?

All the macros are saving as "C:\Temp\AV.xlsx"

Wait I'm so dumb I think I got the first part down now. Now the error shows here:

 

[John_w]

Wrong sheet name - AV Divison

 

[cutemeatball]

Yup got that fixed. Now I'm getting a different error. posted it above ^

Wrong sheet name - AV Division

 

[John_w]

I can't see why that error occurs with the code you've posted.

 

[cutemeatball]

I can't see why that error occurs with the code you've posted.