Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Split one table into multiple sheets based on criteria

  1. #11
    Board Regular
    Join Date
    Jun 2008
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    @Fluff

    YOU ROCK!!! THANKS AGAIN!

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,078
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    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

  3. #13
    Board Regular
    Join Date
    Jun 2008
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    @Fluff, You helped me before I hope you can help me again. I have listed below the macro as it stands today. Is there anyway that the macro after splitting the tabs out, can create its own individual file based on the tab name?

    Sub VendorSplit()
    '
    ' VendorSplit Macro
    '
    Dim Cl As Range
    Dim WS As Worksheet
    Dim Ky As Variant

    Columns("E:E").Select
    Selection.Replace What:="FULL ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:="TPP", Replacement:="TRANSFER", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Range("A1").Select

    Set WS = Sheets("Data")
    With CreateObject("scripting.dictionary")
    For Each Cl In WS.Range("O2", WS.Range("O" & Rows.Count).End(xlUp))
    .Item(Cl.Value) = Empty
    Next Cl
    For Each Ky In .Keys
    WS.Range("A1:O1").AutoFilter 15, Ky
    Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
    WS.AutoFilter.Range.SpecialCells(xlVisible).EntireRow.Copy Range("A1")
    Next Ky

    Dim Wsht As Worksheet
    For Each Wsht In Worksheets
    With Wsht.UsedRange
    .EntireColumn.AutoFit
    End With
    Next Wsht

    Range("A1").Select
    Sheets("Data").Select
    Range("DataTable[[#Headers],[Original Producer Number]]").Select
    ActiveSheet.ListObjects("DataTable").Range.AutoFilter Field:=15
    ActiveWindow.SmallScroll Down:=-30

    Cells.Select
    Selection.ColumnWidth = 14
    Range("DataTable[[#Headers],[Original Producer Number]]").Select

    For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
    If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
    Sheets(j).Move after:=Sheets(j + 1)

    Sheets("Data").Select
    Sheets("Data").Move Before:=Sheets(1)

    End If
    Next
    Next
    End With
    End Sub

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,078
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    As this is now a totally different question, please start a new thread.
    Thanks
    - 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. #15
    Board Regular
    Join Date
    Jun 2008
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    Ok, thanks

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
  •