Consulting

Results 1 to 4 of 4

Thread: Trying to copy multiple worksheets into new workbooks

  1. #1

    Trying to copy multiple worksheets into new workbooks

    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

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    Sub SplitSheets2()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim shn As String
         
        Set wb = ActiveWorkbook
        shn = "SWAWP Rubric"
        
        wb.Sheets(shn).Move wb.Sheets(1)
          
        For Each ws In wb.Sheets
            If ws.Name <> shn Then
                Sheets(Array(shn, ws.Name)).Copy
                With ActiveWorkbook
                    .SaveAs Filename:=wb.Path & "\" & ws.Name & " SWAWP Scoring Sheet" & ".xlsx"
                    .Close False
                End With
            End If
        Next
    
    End Sub
    マナ

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    >wb1.Activate
    >wb1.swawp.Copy Before:=wb2.ws 'error 438 here


    swawp.Copy Before:=wb2.Sheets(1)

  4. #4
    Thank you mana. That worked like a charm!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •