Schaffer621
10-20-2017, 02:01 PM
Hello, I am relatively new to VBA and am having an issue. I have a workbook with about 90 sheets in it, one of them being named "SWAWP Rubric". I am trying to separate the workbook so that every worksheet is its own workbook, but also include the worksheet "SWAWP Rubric" as the first sheet in every new workbook. It would look like this , WB1 ("SWAWP Rubric", Sheet 1), WB2 ("SWAMP Rubric", Sheet 2), etc.. I have been able to break every sheet into a new workbook in the past, but trying to add the "SWAWP Rubric" sheet in front of each them is where I am getting hung up. Any help on getting this to work would be most appreciated!
Option Explicit
Sub SplitSheets()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, swawp As Worksheet
Dim sPath1 As String
Set wb1 = ActiveWorkbook
Set swawp = wb1.Sheets("SWAWP Rubric")
sPath1 = wb1.Path
Application.ScreenUpdating = False
For Each ws In wb1.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=sPath1 & "\" & ws.Name & " SWAWP Scoring Sheet" & ".xlsx"
Set wb2 = ActiveWorkbook
wb1.Activate
wb1.swawp.Copy Before:=wb2.ws 'error 438 here
Call wb2.Close(False)
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Option Explicit
Sub SplitSheets()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, swawp As Worksheet
Dim sPath1 As String
Set wb1 = ActiveWorkbook
Set swawp = wb1.Sheets("SWAWP Rubric")
sPath1 = wb1.Path
Application.ScreenUpdating = False
For Each ws In wb1.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=sPath1 & "\" & ws.Name & " SWAWP Scoring Sheet" & ".xlsx"
Set wb2 = ActiveWorkbook
wb1.Activate
wb1.swawp.Copy Before:=wb2.ws 'error 438 here
Call wb2.Close(False)
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub