Results 1 to 5 of 5

Thread: Splitting Data from one Table into Multiple Tables

  1. #1

    Splitting Data from one Table into Multiple Tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey everyone.

    I have been searching for a couple days now to see if anyone has asked this question but I could not find the answer. I was able to get code that will split the data in one worksheet and move it into separate (already existing) worksheets. However what I am trying to do now is to have a table of data split into other tables based on a column value. For Example: if column A = SLC then move it to the table named "SLC".

    This is the code that I have used to move the information from a worksheet to multiple worksheets based on column A. Could anyone help me tweek this to work or provide different code that will work? S
    Code:
    ub Sort()
    '
    ' Sort Macro
    
    Dim sh As Worksheet, ws As Worksheet, lr As Long, lc As Long, rng As Range, c As Range
    Set sh = Sheets("Detail Data")
    lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
    lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    sh.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2), True
    Set rng = sh.Range("A" & lr + 3, sh.Cells(Rows.Count, 1).End(xlUp))
        For Each c In rng
            Set ws = Sheet(c.Value)
            sh.Range("A1", sh.Cells(lr, lc)).AutoFilter 3, c.Value
            sh.Range("A2", sh.Cells(lr, lc)).SpecialCells(xlCellTypeVisible).Copy ws.Range("A4")
        Next
    sh.AutoFilterMode = False
    sh.Range("A" & lr + 2, sh.Cells(Rows.Count, 1).End(xlUp)).Delete
    End Sub
    Any help would be great. Thanks.

  2. #2
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas, no VBA macro, no Reference Designators.
    With dynamic Tables, not named ranges.
    http://www.mediafire.com/view/j9vcbw...05_15_15b.xlsx

  3. #3
    Quote Originally Posted by Herbds7 View Post
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas, no VBA macro, no Reference Designators.
    With dynamic Tables, not named ranges.
    I'm sorry, I wont be able to use your suggestion. My work does not allow installing any add on packages or software on our machines. Any other ideas?

  4. #4

    Same Issue

    Hey
    I am working on something very similar.. were you able to find a macro?

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    bonescoster ,this is a cross post (you have posted the same query elsewhere) without including links to where you have done so.

    Please have a careful read of http://www.excelguru.ca/content.php?184 to understand why this important, then make the necessary additions of links to all sites/threads.
    Ultimately, bonescoster, it will be to your benefit.

    To others, I would prefer if people considering responding to this thread would delay doing so until bonescoster has complied.

Posting Permissions

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