Macro to create seperate sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony - 10 Dec 2007 08:12 GMT

I have one worksheet containing a list of all sales from all salespeople,
salesperson's name is in column B with other details in further columns.

Eg Master sheet
Col B Col C Col D Col E
Salesperson Prodcuct Amount Discount
Simon XYZ 500 50
Simon ABC 600 50
Simon 123 400 10
Olivia ABC 650 50
Olivia DEF 660 60

I would like to break out Simons details onto one sheet and Olivias onto
another (seperate sheets already exist and with formulas in certain cells) so
that they appear on the new sheet in say range starting in C10 down.
The number of times that Simon and Olivia would appear would vary every time
the master sheet is created.

Any help would be much appreciated.

Thanks

Reply to this Message

Bob Phillips - 10 Dec 2007 09:29 GMT

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow + 1
If .Cells(i, TEST_COLUMN).Value <> .Cells(i - 1,
TEST_COLUMN).Value Then
If i > 2 Then
.Cells(StartRow, TEST_COLUMN).Resize(i - StartRow,
4).Copy sh.Range("C11")
End If
If i <= LastRow Then
Set sh = Worksheets(.Cells(i, TEST_COLUMN).Value)
sh.Cells.ClearContents
.Range("B1:E1").Copy sh.Range("C10")
StartRow = i
End If
End If
Next i

End With

End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have one worksheet containing a list of all sales from all salespeople,
> salesperson's name is in column B with other details in further columns.
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message