Need help with VBA Loop (please)

[hellfire45]

Thanks for reading. Allow me to explain what is happening. I have a spreadsheet into which rows of data are imported daily. I also have 10 people who need to receive the work evenly distributed. I need the program to:

copy data row 1 and paste the data to worker worksheet 1, then,
copy data row 2 and paste the data to worker worksheet 2, then,
copy data row 3 and paste the data to worker worksheet 3, then,
copy data row 2 and paste the data to worker worksheet 4, then,
copy data row 2 and paste the data to worker worksheet 5, etc...
After the 10th worker gets a row of data, I need it to go back and start over again with worker number 1.
I need this process to occur until everybody has 120 distributions OR until the raw data pile runs out of rows.

I think maybe this isn't that hard but I'm a newbie. Can anybody help me out with this?

Thank you so much!

 

[onlyadrafter]

Hello,

Its been a while since I've been here, and its been a long day.

How about this

Sub COPY_TO_SHEETS()
    MY_LAST_ROW = ActiveSheet.UsedRange.Rows.Count
    If MY_LAST_ROW > 1200 Then MY_LAST_ROW = 1200
    For MY_ROWS = 1 To MY_LAST_ROW
        Rows(MY_ROWS).Copy
        MY_SHEETS = ((Right(MY_ROWS, 1)) + 2) * 1
            Sheets(MY_SHEETS).Range("A65536").End(xlUp).Offset(1).PasteSpecial (xlValues)
    Next MY_ROWS
End Sub

This assumes you core data is the first tab on the left, and there are 10 further tabs.

 

[hellfire45]

I think its a good first attempt but I don't think it works quite right. Thanks for the effort. Let me explain some more.

Today, I have 5700 rows (it will be different everyday, so I need the loop to simply run until all rows are done). These rows start in row 2 and go from column A until Y.

I need these rows to be distributed onto 11 different worksheets. I don't want them to overwrite each other so after row 2 on all 11 worksheets are filled, want the loop to continue but put the data on row 3. In this way, each of the 11 worksheets ends up with 518 rows of data ranging from row 2 until row 519.

When the 5700 rows are completed, the loop stops.

Any ideas guys?

 

[onlyadrafter]

Hello,

how about

Sub COPY_TO_SHEETS()
    Application.ScreenUpdating = False
    MY_SHEETS = 2
    For MY_ROWS = 1 To ActiveSheet.UsedRange.Rows.Count
        Rows(MY_ROWS).Copy
            Sheets(MY_SHEETS).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
            MY_SHEETS = MY_SHEETS + 1
            If MY_SHEETS = 13 Then MY_SHEETS = 2
    Next MY_ROWS
    Application.ScreenUpdating = True
End Sub

but you did ask:

I need this process to occur until everybody has 120 distributions OR until the raw data pile runs out of rows.

 

[hellfire45]

This works great! thank you so much!