One Worksheet to Many

[HilcrRWise]

I have an excel worksheet layed out as follows (numbers = row):

1 - Section Title
2 - Section data 1
3 - Section data 2
4 - Section data 3
etc
20 - blank
21 - Section Title
22 - section data 1
etc

all the different sections are divided by a blank row.
There are no blank rows within each section.
Each section contains a different number of rows.

What I want to do is convert this one worksheet in to many with th
section title as the worksheet name, and each worksheet only listin
the data in its specific section. Is there a quick and easy way to d
this without having to spend ages cutting and pasting

 

[JE McGimpsey]

One way:

Public Sub Sectionalize()
Dim rDest As Range
Dim i As Long
Dim bNewSheet As Boolean

Application.ScreenUpdating = False
bNewSheet = True
With ActiveSheet
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
With .Cells(i, 1)
If IsEmpty(.Value) Then
bNewSheet = True
Else
If bNewSheet Then
On Error Resume Next
Worksheets.Add(After:=Sheets( _
Sheets.Count)).Name = .Value
On Error GoTo 0
Set rDest = ActiveSheet.Range("A1")
bNewSheet = False
Else
rDest.Value = .Value
Set rDest = rDest.Offset(1, 0)
End If
End If
End With
Next i
.Activate
End With
Application.ScreenUpdating = False
End Sub

 

[HilcrRWise]

Thanks for the reply.

The code you provided works great, but how do I go about modifying i
so the following is taken in to account:

1. The section title is to be copied in to the new worksheet as well a
the data.

2. The Section Title needs to be formated in bold.

3. The section data rows have multiple columns, these need to be copie
over aswell.

Many Thank

 

[HilcrRWise]

Is anyone able to help me modify this code (see previous reply)