Over my head I fear

[chefdt]

I have a multi-sheet workbook. Each worksheet has sales data from many different salesman. Each set of data separated by a blank row. Each row of salesman data has a unique identifier, I'll call "SALESID"

I can generate additional tabs...one for each salesman, driven off of a separate index sheet, but the problem I'm having, is moving the rows of data from one sheet to another so that each salesman will have his or her own unique worksheet with just their data. Currently I have it organized by Sales District, with each district having it's own worksheet, and each worksheet containing data for many salesmen.


The sheet looks like this;

SDL 016765 City Medical Center $4,583 260
SDK 267564 Charro's Restaurant $3,445 155

Every line is differnt, with data grouped by the first column identifier.

 

[ravishankar]

Hi
Insert as many sheets as the number of salesman with their ID as the sheet name. paste the following codes in the macro window(Alt F11 insert > module) Sub distribute()

Dim a As Integer, b As Integer, c As Integer, x As Integer, y As Integer
For a = 1 To 6 ' assuming 6 districts
x = Worksheets(a).Cells(Rows.Count, 2).End(xlUp).Row 'assuming sales ID is in row 2
For b = 2 To x
Worksheets(a).Rows(b).Copy
c = Worksheets(a).Cells(b, 2)
y = Worksheets(c).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(c).Rows(y + 1).PasteSpecial
Next b
Next a
End Sub

Run the macro. It walks through each row of data in each district, copies it and pastes to respective sheet of salesmen's ID. Might be slow depending on no of rows to be copied.
Ravi