Macro needed for extracting to Tab
Upcoming Power Excel Seminars
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Macro needed for extracting to Tab

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Macro needed for extracting to Tab

     
    Hi all,

    I am wondering if there is a way of extracting a range of cells into seperate tabs from the one long sheet.

    Let me give you my issue,

    I have one sheet with alot of tables and data, Someone glued it all together and i need each table into separate tabs,

    At present it is currently like this:

    SOB BBEE
    TEST 1 TEST1
    TEST 2 TEST3
    TEST 3 TEST3
    SOB BBEE
    TEST11 TEST11
    TEST12 TEST13


    So Anywhere Cell A & B say SOB & BBEE it is a new table all the way down until it says it again. The tables vary in length and they are not all the same. but there is 250+ tables all glued like this (idiotic) but i am screaming for help at this stage if anyone can please


    Thanks!

  2. #2
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Quote Originally Posted by Keithheavey View Post
    Hi all,

    I am wondering if there is a way of extracting a range of cells into seperate tabs from the one long sheet.

    Let me give you my issue,

    I have one sheet with alot of tables and data, Someone glued it all together and i need each table into separate tabs,

    At present it is currently like this:

    SOB BBEE
    TEST 1 TEST1
    TEST 2 TEST3
    TEST 3 TEST3
    SOB BBEE
    TEST11 TEST11
    TEST12 TEST13


    So Anywhere Cell A & B say SOB & BBEE it is a new table all the way down until it says it again. The tables vary in length and they are not all the same. but there is 250+ tables all glued like this (idiotic) but i am screaming for help at this stage if anyone can please


    Thanks!
    I tried to do a macro there but i think i need to tell it to cut before the SOB & BBEE line but i cant figure it out.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,037
    Post Thanks / Like
    Mentioned
    142 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Macro needed for extracting to Tab

    How about
    Code:
    Sub Splitdata()
    
       Dim Ws As Worksheet
       Dim Ar As Areas
       Dim Rng As Range
       
       Set Ws = ActiveSheet
       With Ws.Range("A1", Ws.Range("A" & Rows.Count).End(xlUp))
          .Replace "SOB", "=XXSOB", xlWhole, , False, , False, False
          Set Ar = .SpecialCells(xlConstants).Areas
          .Replace "=XX", "", xlPart, , False, , False, False
          For Each Rng In Ar
             Worksheets.Add , Sheets(Sheets.Count)
             Rng.Offset(-1).Resize(Rng.Count + 1).EntireRow.Copy ActiveSheet.Range("A1")
          Next Rng
       End With
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub Splitdata()
    
       Dim Ws As Worksheet
       Dim Ar As Areas
       Dim Rng As Range
       
       Set Ws = ActiveSheet
       With Ws.Range("A1", Ws.Range("A" & Rows.Count).End(xlUp))
          .Replace "SOB", "=XXSOB", xlWhole, , False, , False, False
          Set Ar = .SpecialCells(xlConstants).Areas
          .Replace "=XX", "", xlPart, , False, , False, False
          For Each Rng In Ar
             Worksheets.Add , Sheets(Sheets.Count)
             Rng.Offset(-1).Resize(Rng.Count + 1).EntireRow.Copy ActiveSheet.Range("A1")
          Next Rng
       End With
    End Sub
    That didnt work,

    I get a RunTime error '1004: Application defined or object- defined error

    It tells me that there is an error in this line:

    Rng.Offset(-1).Resize(Rng.Count + 1).EntireRow.Copy ActiveSheet.Range("A1")

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,037
    Post Thanks / Like
    Mentioned
    142 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Do any new sheets get created & filled with data?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,037
    Post Thanks / Like
    Mentioned
    142 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Another option, if the tables have their own names, would be
    Code:
    Sub SplitTbl()
    
       Dim tbl As ListObject
       
       For Each tbl In ActiveSheet.ListObjects
          Worksheets.Add , Sheets(Sheets.Count)
          tbl.DataBodyRange.Copy ActiveSheet.Range("a1")
       Next tbl
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Quote Originally Posted by Fluff View Post
    Do any new sheets get created & filled with data?
    It creates a new sheet but it is blank.

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Quote Originally Posted by Fluff View Post
    Another option, if the tables have their own names, would be
    Code:
    Sub SplitTbl()
    
       Dim tbl As ListObject
       
       For Each tbl In ActiveSheet.ListObjects
          Worksheets.Add , Sheets(Sheets.Count)
          tbl.DataBodyRange.Copy ActiveSheet.Range("a1")
       Next tbl
    End Sub
    That doesnt work either i'm afraid., They are not in as tables on excel, I just call them tables in work.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    8,037
    Post Thanks / Like
    Mentioned
    142 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Macro needed for extracting to Tab

    Ok, is SOB on its own in the cell, is it part of a string?
    If it's on its own step through the original code using F8, until you get to this line
    Code:
          Set Ar = .SpecialCells(xlConstants).Areas
    then have a look at the sheet, where you had SOB what does it say now?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro needed for extracting to Tab

      
    Quote Originally Posted by Fluff View Post
    Ok, is SOB on its own in the cell, is it part of a string?
    If it's on its own step through the original code using F8, until you get to this line
    Code:
          Set Ar = .SpecialCells(xlConstants).Areas
    then have a look at the sheet, where you had SOB what does it say now?
    SOB is in its own "Merged Cell"

    When i go though with it using F8 Nothing, It still says SOB...

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
  •  

 

 
DMCA.com