Help With loop macro??

P

pauluk

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 o
rows then place it in to seprate workbooks.

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

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

Regards
Pau
 
T

TH

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
 
Top