Sylvia,
Slight correction

'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock =
..Range(StartBlock.End(xlDown).Address)

NickHK

"NickHK" wrote in message
...
Sylvia,
You should add error trapping:
<Code
Private Sub CommandButton1_Click()
Dim StartBlock As Range
Dim CopyBlock As Range
Dim NewWS As Worksheet

With ActiveSheet
'Assuming start is in A1
Set StartBlock = .Range("A1")
'If this blank, we need the first non-blank cell
If StartBlock.Value = "" Then Set StartBlock =
..Range(StartBlock.Address
& ":" & StartBlock.End(xlDown).Address)

'Don't hard the Max Rows as Excel 12 etc will have 65K rows
Do Until StartBlock.Row = .Rows.Count

'Get the next block, before the next blank row
Set CopyBlock = .Range(StartBlock.Address & ":" &
StartBlock.End(xlDown).Address)

'Uncomment to see the Range that will be copied
'.Select
'CopyBlock.Select

'Create new WS at the end
Set NewWS = ThisWorkbook.Worksheets.Add(,
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt))

'Assuming each block goes to A1
CopyBlock.Copy NewWS.Range("A1")

'Move StartBlock to the end of the CopyBlock
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
'Now move to the start of the next block
Set StartBlock = .Range(StartBlock.End(xlDown).Address)
Loop
End With
End Sub
</Code

NickHK

"Sylvia" wrote in
message ...

Thanks for your help. But how can I copy the data between two blank
rows? I mean whot will I specify in loop? Because depending upon that I
should generate the number of excel tabs.


--
Sylvia
------------------------------------------------------------------------
Sylvia's Profile:
http://www.excelforum.com/member.php...o&userid=30300
View this thread:
http://www.excelforum.com/showthread...hreadid=500029