VBA Code to Add Worksheet to Workbooks that Split
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA Code to Add Worksheet to Workbooks that Split

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to Add Worksheet to Workbooks that Split

    Hi!

    I have a code that splits a workbook into multiple workbooks based on unique values in the first column found in Sheet1, however I just want it to also pull the second sheet (Sheet2) that is within the original workbook into all of the workbooks that split.

    Any help would be greatly appreciated, the code can be found below. Thank you! :
    Sub CreateWorkbooks()

    Application.ScreenUpdating = False
    Dim LastRow As Long, super As Range, RngList As Object, item As Variant, srcWB As Workbook, srcWS As Worksheet
    Set srcWB = ThisWorkbook
    Set srcWS = srcWB.Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    With srcWS
    For Each Rng In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    If Not RngList.Exists(Rng.Value) Then
    RngList.Add Rng.Value, Nothing
    End If
    Next
    End With
    For Each item In RngList
    srcWS.Copy
    With Cells(1).CurrentRegion
    .AutoFilter 1, "<>" & item
    ActiveSheet.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    ActiveWorkbook.SaveAs Filename:=srcWB.Path & Application.PathSeparator & item & ".xlsx", FileFormat:=51
    ActiveWorkbook.Close False
    End With
    Next item
    Application.ScreenUpdating = True

    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    How about
    Code:
       For Each item In RngList
          Sheets(Array(srcWS, "Sheet2")).Copy
          With Sheets("Sheet1")
             .Cells(1).CurrentRegion.AutoFilter 1, "<>" & item
             .AutoFilter.Range.Offset(1, 0).EntireRow.Delete
             If .AutoFilterMode Then .AutoFilterMode = False
             ActiveWorkbook.SaveAs FileName:=srcWB.Path & Application.PathSeparator & item & ".xlsx", FileFormat:=51
             ActiveWorkbook.Close False
          End With
       Next item
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    Hi!

    I tried running this code and I got a mismatch error.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    Which line gave the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    Sorry, the Sheets(Array(srcWS, "Sheet2")).Copy gave the mismatch error.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    Oops, it should be
    Code:
         Sheets(Array(srcWS.Name, "Sheet2")).Copy
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    I put that in, now where it says "For Each item In RngList" it highlights the "For Each Item" as a compile error and says "for control variable already in use"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    You need to replace this part of your code
    Code:
    For Each item In RngList
    srcWS.Copy
    With Cells(1).CurrentRegion
    .AutoFilter 1, "<>" & item
    ActiveSheet.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    ActiveWorkbook.SaveAs Filename:=srcWB.Path & Application.PathSeparator & item & ".xlsx", FileFormat:=51
    ActiveWorkbook.Close False
    End With
    Next item
    with the code I supplied.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    That worked, thank you so much for all of your help, I really appreciate it!

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Add Worksheet to Workbooks that Split

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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