Splitting worksheet data into multiple worksheets/workbooks by ROW criteria

[VBAbeginner2015]

Hi

I'm trying to spilt data in a worksheet into multiple worksheets or multiple workbooks using rows instead of columns. I haven't been able to find any previous threads that do not relate to columns.

Basically I have the following all in one worksheet (called 'Master'):

Blank row
Header
Blank row
Title (which is blue text)
Blank row
Text
text
text
Blank row
Blank row
Title (which is blue text)
Blank row
Text
Blank row

etc...


I'd like to be able to spilt the worksheet by the title (in blue text) so that everything from the blue title to the next blue title is in a new worksheet. Is there any way I can do this please? The text rows can vary in length so there's never a set amount of rows it can be spilt by.

Thanks all

 

[hiker95]

VBAbeginner2015,

Thanks again.

Here is a macro solution for you to consider, based on the screenshots that you have provide.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1
2	Overall Tables
3
4	Plant 1
5
6	Vehicle	Input 1	Input 2	Input 3	Input 4	Input 5	Input 6
7	345
8	564
9	654
10	343
11	978
12	243
13
14
15	Plant 2
16
17	Vehicle	Input 1	Input 2
18	120
19	431
20	545
21
22	Plant 3
23
24	Vehicle	Input 1	Input 2
25	330
26
27	Plant 4
28
29	Vehicle	Input 1	Input 2
30	347
31	809
32	879
33
Master

After the macro in the new worksheets:

Excel 2007
	A	B	C	D	E	F	G	H
1	Plant 1
2
3	Vehicle	Input 1	Input 2	Input 3	Input 4	Input 5	Input 6
4	345
5	564
6	654
7	343
8	978
9	243
10
Plant 1

Excel 2007
	A	B	C	D
1	Plant 2
2
3	Vehicle	Input 1	Input 2
4	120
5	431
6	545
7
Plant 2

Excel 2007
	A	B	C	D
1	Plant 3
2
3	Vehicle	Input 1	Input 2
4	330
5
Plant 3

Excel 2007
	A	B	C	D
1	Plant 4
2
3	Vehicle	Input 1	Input 2
4	347
5	809
6	879
7
Plant 4

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CreatePlants()
' hiker95, 08/20/2015, ME876991
Dim wm As Worksheet, p As String
Dim Area As Range, sr As Long, er As Long, lc As Long, nr As Long
Application.ScreenUpdating = False
Set wm = Sheets("Master")
With wm
  For Each Area In wm.Range("A3", wm.Range("A" & wm.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      If .Rows.Count = 1 Then
        p = wm.Cells(sr, 1).Value
        If Not Evaluate("ISREF('" & Trim(p) & "'!A1)") Then Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Trim(p)
        Sheets(p).Cells(1, 1) = p
        nr = 3
      Else
        lc = wm.Cells(sr, Columns.Count).End(xlToLeft).Column
        wm.Range(wm.Cells(sr, 1), wm.Cells(er, lc)).Copy Sheets(p).Range("A" & nr)
        Sheets(p).Columns(1).Resize(, lc).AutoFit
        Application.CutCopyMode = False
      End If
    End With
    wm.Activate
  Next Area
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CreatePlants macro.

 

[VBAbeginner2015]

This works great! Thank you so much!!

 

[hiker95]

VBAbeginner2015,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.