This was a code which was given to me yesterday by Nick. This copyes the
data of one sheet in different tabs. But this applies to first column
only. How to copy the entire row instead of only column a?

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.End(xlDown).Address)
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


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

Sylvia,

Set CopyBlock = .Range(StartBlock.Address & ":" &
StartBlock.End(xlDown).Address).EntireRow

NickHK

"Sylvia" wrote in
message ...

This was a code which was given to me yesterday by Nick. This copyes the
data of one sheet in different tabs. But this applies to first column
only. How to copy the entire row instead of only column a?

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.End(xlDown).Address)
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


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