ExcelBanter - Help With loop macro??

Try a loop FOR/NEXT or WHILE/WEND using MOD and .row

It would go something like this:
--------------------------------
Sub moveData()

Dim i As Integer
Dim x As Integer
Dim mySheet As Integer
Dim mySheetRow As Integer

i = 1 'start at row 1

While Cells(i, 1) ""
x = Cells(i, 1).Row Mod 8
If x = 1 Then
mySheet = 1 'Start over every 8
mySheetRow = mySheetRow + 1 'Increment every 8
If mySheetRow = 0 Then mySheetRow = 8 'Klugy, but otherwise it
returns 0 for the 8th row.
Else
mySheet = mySheet + 1
End If
Cells(i, 2) = "Sheet " & mySheet & " row " & mySheetRow 'Demos results
in column B
'Replace the above demo assignment with something like the next line to
copy data to the other sheets
'Worksheets(mySheet + 1).Cells(mySheetRow, 1) = Cells(i, 1)
'Note that mySheet is an integer and as such will refer to the sheet
NUMBER.
'The above assumes it is sheets 2 thru 9 with sheet(1) being your data
sheet.
'You can change it to text to refer to a sheet named "1"
i = i + 1 'Increment the row counter
Wend

End Sub

'NOTES: This doesn't create sheets 1-8
' This assumes all your data is in column A of the active sheet
' This assumes your data to be copied starts in Row 1

Regards,
TH
--------------------------------
On 4/15/04 15:48, in article , "pauluk "
wrote:

Hi Everybody :-)

What i am lookin for is help\guidance with loop macro.

What i need is for the macro to run throug the data held in a number of
rows then place it in to seprate workbooks.

i.e. copies data from first row into workbook 1.xls then copies second
row to work book 2.xls this goes on till row 8 and that row has been
copied to 8.xls then 9th row would be copied to 1.xls etc. up until
all the data has been placed in to seprate workbooks. (its a bit like
one for you and one for me one for you...)

Can anybody give me any help or point me in the right direction.

Regards
Paul

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