# Over my head I fear

#### chefdt

##### Board Regular
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### ravishankar

##### Well-known Member
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()
Code:
``````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

Replies
12
Views
503
Replies
3
Views
148
Replies
9
Views
349
Replies
4
Views
113
Replies
1
Views
533

1,190,633
Messages
5,982,040
Members
439,751
Latest member
sohamkhatri

### 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?

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