Macro needed for extracting to Tab

KiloHotel

New Member
Joined
Feb 4, 2018
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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:

SOBBBEE
TEST 1TEST1
TEST 2TEST3
TEST 3TEST3
SOBBBEE
TEST11TEST11
TEST12TEST13

<tbody>
</tbody>


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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:

SOBBBEE
TEST 1TEST1
TEST 2TEST3
TEST 3TEST3
SOBBBEE
TEST11TEST11
TEST12TEST13

<tbody>
</tbody>


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.
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
Do any new sheets get created & filled with data?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top