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

 

[Sinon]

Excel's pivot table has an option to split a table into multiple worksheets. It's called "Show report filter pages" but it would mean possibly rearranging your data to do this. This shows how to use this option: What are Pivot Table Report Filters and How to use them? | Chandoo.org - Learn Microsoft Excel Online
Alternatively, a macro could be created by someone who isn't a complete noob in VBA (like myself).

 

[hiker95]

VBAbeginner2015,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, sheet name, cells, cell formatting, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, rows, columns.


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[VBAbeginner2015]

Hi

Thanks for your comments. Sorry new to this!

I'm using Excel 2013 with Windows 7 on a PC.

An example of my data is:

Excel 2012
	A	B	C
1
2	Overall Tables
3
4	Plant 1
5
6	Vehicle	Input 1	Input 2
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
Sheet1

And I want each Plant section in a new tab i.e:

Excel 2012
	A	B	C
1	Plant 1
2
3	Vehicle	Input 1	Input 2
4	345
5	564
6	654
7	343
8	978
9	243
Sheet2

and:

Excel 2012
	A	B	C
1	Plant 2
2
3	Vehicle	Input 1	Input 2
4	120
5	431
6	545
Sheet3

Is that more helpful?

 

[hiker95]

VBAbeginner2015,

Nicely done, thanks.

Can we put each Plant # in a new sheet, with the new worksheet name like Plant #?

 

[VBAbeginner2015]

Yes that would be even better, thanks

 

[hiker95]

VBAbeginner2015,

Does worksheet Master actually look like this?

Excel 2007
	A	B	C
1
2	Overall Tables
3
4	Plant 1
5
6	Vehicle	Input 1	Input 2
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
Sheet1

Or, like this?

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

 

[VBAbeginner2015]

It looks like the first table you pasted

 

[hiker95]

VBAbeginner2015,

Can we have another screenshot of worksheet Master, with:

1. All the Input areas filled in?

2. Can there be more then 2 Input areas for each vehicle?

2a. If so, can we see some examples?

 

[VBAbeginner2015]

Thanks for your reply. Please see below:

Excel 2012
	A	B	C	D	E	F	G
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	Text	Text
8	564	Text	Text
9	654	Text	Text
10	343	Text	Text
11	978	Text	Text
12	243	Text	Text
13
Sheet1

The input areas for 3 to 6 won't be filled in on this sheet (they will remain blank). I need to spilt the worksheet to send it out to various areas for them to fill in the blank parts. The information on input 1 and 2 can differ dependant on the area. This information is pulled from a programme into excel like this which upto now I have then been splitting manually. (I can't change the look of how its pulled into excel as it is sent to me like this)