Need help with VBA Loop (please)

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
447
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!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

How about this

Code:
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

Active Member
Joined
Jun 7, 2014
Messages
447
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

how about

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top