distribute rows from one workbook to other workbooks

[Guest]

I need to distribute the rows from a master workbook to other workbooks. I
need to do this one row (or small group of rows) at a time. To make the
distribution even, I cannot take the total number of rows and diveid it by
the number of sheets I need (9). IS ther a way to do this?

 

[Bernie Deitrick]

JD,

Do you have a criteria that you will base the row distrubution upon?

HTH,
Bernie
MS Excel MVP

 

[Guest]

No, the file just needs to be distributed across the 9 users. It could be as
first row to the first user, second row to second user, etc... When the
tenth row cam around, it would be assigned to the first user. Other than
just trying to be even, there are really no criteria that will be used for
the distribution of rows.

 

[Bernie Deitrick]

JD,

Try the macro below. This assumes that the data table starts in A1, with headers in row 1.

If you need to change the number of new workbooks or the names used, change the coded values:

ShtCnt = 9
ShtName = "Split "


HTH,
Bernie
MS Excel MVP

Option Explicit
Dim i As Long
Dim r As Long
Dim j As Integer
Dim ShtCnt As Integer
Dim ShtName As String
Dim Wkbk As Workbook
Dim Sht As Worksheet

Sub SplitSheet()

ShtCnt = 9
ShtName = "Split "

Set Wkbk = ActiveWorkbook
Set Sht = ActiveSheet
r = Sht.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To ShtCnt
Worksheets.Add.Name = ShtName & i
Sht.Rows(1).Copy _
Sheets(ShtName & i).Cells(1, 1).EntireRow
Next i

For i = 2 To r
j = ((i - 2) Mod ShtCnt) + 1
Sht.Rows(i).Copy _
Sheets(ShtName & j).Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
Next i

For i = 1 To ShtCnt
Wkbk.Activate
Wkbk.Sheets(ShtName & i).Move
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ShtName & i & ".xls"
Next i

End Sub