Macro needed for extracting to Tab

[KiloHotel]

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:

[TABLE="width: 200"]
<tbody>[TR]
[TD]SOB[/TD]
[TD]BBEE[/TD]
[/TR]
[TR]
[TD]TEST 1[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]TEST 2[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]TEST 3[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]SOB[/TD]
[TD]BBEE[/TD]
[/TR]
[TR]
[TD]TEST11[/TD]
[TD]TEST11[/TD]
[/TR]
[TR]
[TD]TEST12[/TD]
[TD]TEST13[/TD]
[/TR]
</tbody>[/TABLE]


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!

 

[KiloHotel]

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:

[TABLE="width: 200"]
<tbody>[TR]
[TD]SOB[/TD]
[TD]BBEE[/TD]
[/TR]
[TR]
[TD]TEST 1[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]TEST 2[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]TEST 3[/TD]
[TD]TEST3[/TD]
[/TR]
[TR]
[TD]SOB[/TD]
[TD]BBEE[/TD]
[/TR]
[TR]
[TD]TEST11[/TD]
[TD]TEST11[/TD]
[/TR]
[TR]
[TD]TEST12[/TD]
[TD]TEST13[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Fluff]

How about

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

 

[KiloHotel]

How about

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")

 

[Fluff]

Do any new sheets get created & filled with data?

 

[Fluff]

Another option, if the tables have their own names, would be

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

 

[KiloHotel]

Do any new sheets get created & filled with data?

It creates a new sheet but it is blank.

 

[KiloHotel]

Another option, if the tables have their own names, would be

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.

 

[Fluff]

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

      Set Ar = .SpecialCells(xlConstants).Areas

then have a look at the sheet, where you had SOB what does it say now?

 

[KiloHotel]

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

      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...