Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create seperate sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create seperate sheets
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 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tony" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add two cells from seperate work sheets into a cell on seperate wo | Excel Worksheet Functions | |||
macro to move and save all sheets to seperate workbooks | Excel Programming | |||
Macro to copy Column 1 of all sheets to a seperate sheet. | Excel Programming | |||
Use a macro to create new sheets | Excel Programming | |||
Macro to query data from a succession of websites and display in seperate sheets | Excel Programming |