Copy rows untill blank row then paste into new sheet

[Chris Salcedo]

Here where I work I get a report from an mrp system that is formated in
the folowing way:

Project Task Cost Backlog Wip (This is a header)
Act. Act. (also header)
Proj-0001 100 50 50
100 Engineering
info1 1252
info2 2258
200 Manufact.
info1 2252
info2 2252
(Blank Row)
Proj-0002 199 49 50
100 Engineering
info1 1252
info2 5258
200 Manufact.
info1 2452
info2 2220
(Blank Row)
Proj-0003 145 33 89
100 Engineering
info1 1752
info2 5888
200 Manufact.
info1 5852
info2 2249

ETC....(about 300 diferent projects)

The only constants in the report are the number of columns (always A to
P)
the blank line at the end of each project as a separator and the first
2 rows that are header info.

What I need to do is the following; select from the first row that has
info in the first cell (Proj-001 in example) to the blank row then copy
this into a newly created sheet. This continues until the end of the
the worksheet. This will probably create about 300 worksheets.

The first 2 rows of the original data file are header rows and also
must be included in each new worksheet...

Any help would be greatly apreciated.....

Thanks

 

[Tom Ogilvy]

Assuming that the project line contains data in column 3 (hard to see where
things are in your posting - it appears that column 3 is populated for each
line of a project.

Sub copyData()
Dim rng as Range
Dim rng1 as Range
set rng = Range(cells(3,"C"),cells(rows.count,3).End(xlup))
set rng1 = rng.specialcells(xlConstants)
for each ar in rng1.Areas
set sh = worksheets.add(after:=worksheets(worksheets.count))
ar.entirerow.copy Destination:=sh.Range("A1")
sh.Name = ar(1).offset(0,-2).Value
Next
end sub

 

[Chris Salcedo]

This is a very simple example of what I realy have. The range can be
from 20 rows to over 1000 rows...

The data is formated in a visual manner so there is no column that
always has data.

This is how to do it

Skip first 2 rows (These are the header)
Start of range is --- rowX column A has data (This is the project name)
End of range is ---- Blank row ( or next time rowX column A has data)

Copy first 2 rows and the range to a new sheet- repeat to end of data

This is the idea

Thanks for your help...