Macro to split workbook into several based on cell contents
 

Hi,

I have a worksheet with all the sales data by salesperson for ten
branches. Column A is Branch Name, B is Sales Person, C is sales
figures.

I want to create a macro which splits this into ten workbooks, named by
branchname.


Can anyone suggest the easiest way ?



Rich

Macro to split workbook into several based on cell contents
 

Hi

Would this work?
Sub create_worksheet()
branch = "Branch Name"
sale = "Sales Persons"
figur = "Sales Figures"

rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 2 To rowcount
'Assuming the info is on sheet1, if not change the name
Sheets("sheet1").Activate
Range("a" & i).Select
w_name = ActiveCell.Value
Range("b" & i).Select
salep = ActiveCell.Value
Range("c" & i).Select
salef = ActiveCell.Value
Worksheets.Add
ActiveSheet.Name = w_name
ActiveSheet.Activate
Range("a1").Select
ActiveCell = branch
Range("a2").Select
ActiveCell = w_name
Range("b1").Select
ActiveCell = sale
Range("b2").Select
ActiveCell = salep
Range("c1").Select
ActiveCell = figur
Range("c2").Select
ActiveCell = salef

Next
'Assuming the info is on sheet1, if not change the name
Sheets("sheet1").Activate
End Su

--
jette
-----------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...fo&userid=1753
View this thread: http://www.excelforum.com/showthread.php?threadid=56320

Macro to split workbook into several based on cell contents
 

I did this from a pivot table and used Show Pages. I then told Excel to copy
the page to a new workbook and save as sheet.name.

" wrote:

Hi,

I have a worksheet with all the sales data by salesperson for ten
branches. Column A is Branch Name, B is Sales Person, C is sales
figures.

I want to create a macro which splits this into ten workbooks, named by
branchname.


Can anyone suggest the easiest way ?



Rich

Macro to split workbook into several based on cell contents
 

Try this
http://www.rondebruin.nl/copy5.htm

Try the workbook example

--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ps.com...
Hi,

I have a worksheet with all the sales data by salesperson for ten
branches. Column A is Branch Name, B is Sales Person, C is sales
figures.

I want to create a macro which splits this into ten workbooks, named by
branchname.


Can anyone suggest the easiest way ?



Rich