What do i need to change in this piece of code so it copies the whol
row and not just the first column of the row?

Sub movedata()
Dim i As Integer
Dim x As Integer
Dim mySheet As Integer
Dim mySheetRow As Integer

i = 1
While Cells(i, 1) ""
x = Cells(i, 1).Row Mod 8
If x = 1 Then
mySheet = 1
mySheetRow = mySheetRow + 1
If mySheetRow = 0 Then mySheetRow = 8
Else
mySheet = mySheet + 1
End If
Worksheets(mySheet + 1).Cells(mySheetRow, 1) = Cells(i, 1)
i = i + 1
Wend
End Su

--
Message posted from http://www.ExcelForum.com

Hi Paul,

Change

Worksheets(mySheet + 1).Cells(mySheetRow, 1) = Cells(i, 1)

to

Cells(i, 1).Entirerow.Copy Worksheets(mySheet + 1).Cells(mySheetRow, 1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pauluk " wrote in message
...
What do i need to change in this piece of code so it copies the whole
row and not just the first column of the row?

Sub movedata()
Dim i As Integer
Dim x As Integer
Dim mySheet As Integer
Dim mySheetRow As Integer

i = 1
While Cells(i, 1) ""
x = Cells(i, 1).Row Mod 8
If x = 1 Then
mySheet = 1
mySheetRow = mySheetRow + 1
If mySheetRow = 0 Then mySheetRow = 8
Else
mySheet = mySheet + 1
End If
Worksheets(mySheet + 1).Cells(mySheetRow, 1) = Cells(i, 1)
i = i + 1
Wend
End Sub


---
Message posted from http://www.ExcelForum.com/

Try this:

Cells(i, 1).entirerow.copy
Worksheets(mySheet + 1).Cells(mySheetRow, 1).entirerow.pastespecial
Application.cutcopymode = false 'This gets rid of the "marching ants"

TH

On 4/16/04 3:49, in article , "pauluk "
wrote:

What do i need to change in this piece of code so it copies the whole
row and not just the first column of the row?

Sub movedata()
Dim i As Integer
Dim x As Integer
Dim mySheet As Integer
Dim mySheetRow As Integer

i = 1
While Cells(i, 1) ""
x = Cells(i, 1).Row Mod 8
If x = 1 Then
mySheet = 1
mySheetRow = mySheetRow + 1
If mySheetRow = 0 Then mySheetRow = 8
Else
mySheet = mySheet + 1
End If
Cells(i, 1).entirerow.copy
Worksheets(mySheet + 1).Cells(mySheetRow, 1).entirerow.copy
Application.cutcopymode = false
i = i + 1
Wend
End Sub

---
Message posted from http://www.ExcelForum.com/