need to automate

[Naraine Ramkirath]

I have a fairly large worksheet that contains sales commission information.
I would like to automate that process as it currently takes 5-7 days to
complete the task. Here is what i would like to achieve:



1. sort by column B (this column contains sales rep numbers)

2.copy all data for each rep to a new sheet in the same workbook and give
the sheet name the sales rep's name.

3. format each sheet with Arial font, size 8.5, lines all around the data

4. insert a row at the top of each new sheet

5.put the sales rep name in B1

6.COMPUTE SUM OF COLUMNS L,M,N,O

7.Create a new column in R (give it name %)

8.compute column O as % N in column R



Hope I am clear.



Any help will be greatly appreciated.



Regards,



Naraine

 

[Don Guillett]

Shouldn't take but a minute or so. Sounds more like a project in need of
professional help instead of a ng question. You could start by recording a
macro to sort>loop to add sheets(or copy a template that is already
formatted as desired with formulas predone)>put the name in>. BTW, you
shouldn't need a sheet for each rep when you can just filter a master sheet.

 

[Tom Ogilvy]

Also look here:

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

 

[Naraine Ramkirath]

Don Thanks for your reply. I really need a vba script to perform this task-
i'm not good at macros or vba. also, i do need each sales rep's info in
separate sheets.

Naraine

 

[Naraine Ramkirath]

Thanks Tom. I'm having trouble capturing the range for each rep as it
changes. how do i go about doing this?
e.g. rep A is in range a1:O40
rep B in range a41:O59
rep C in range a60:O190
etc...

 

[Tom Ogilvy]

The page I provided you has code to do that by using an autofilter. Look at
the code that copies data to separate sheets.

 

[Naraine Ramkirath]

Sorry Tom,

I'm still unable to come up with a code. Is it possible to provide a sample?

 

[Tom Ogilvy]

the page I cited provided plenty of samples.

Here is the one I was refering to:

Create a new sheet for all Unique values

This example use AdvancedFilter to copy all rows with the same value in the
first column of
the range Sheets("sheet1").Range("A1").CurrentRegion to a new worksheet.
The sheets will be named after the Unique value.
You see that the last two columns of the worksheet are used to make a Unique
list
and add the CriteriaRange. (you can't use this macro if you use this columns)

Note:
The current region is a range bounded by any combination of blank rows and
blank column.
In my example my table start in A1 (header of the first column) and I use
this to set
the filter range Range("A1").CurrentRegion (Use Ctrl * with A1 selected to
see the filter range)
You can also use another cell then A1 in your table but I like to use the
top left cell of the filter range that
is also the header of the first column.

If you want to sort the worksheets in your workbook then go to Chip
Pearson's webpage for a example
http://www.cpearson.com/excel/sortws.htm


Sub Copy_With_AdvancedFilter_To_Worksheets()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Set ws1 = Sheets("Sheet1") '<<< Change
'Tip : You can also use a Dynamic range name,
'http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'This example filter on the first column in the range (change this
if needed)
'You see that the last two columns of the worksheet are used
'to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the
columns)

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False
WSNew.Columns.AutoFit
Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

That does the hard part. Most of the other can be gotten using the macro
recorder as Don suggested.

 

[Naraine Ramkirath]

Tom, thank you.

the page I cited provided plenty of samples.

Here is the one I was refering to:

Create a new sheet for all Unique values

This example use AdvancedFilter to copy all rows with the same value in the
first column of
the range Sheets("sheet1").Range("A1").CurrentRegion to a new worksheet.
The sheets will be named after the Unique value.
You see that the last two columns of the worksheet are used to make a Unique
list
and add the CriteriaRange. (you can't use this macro if you use this columns)

Note:
The current region is a range bounded by any combination of blank rows and
blank column.
In my example my table start in A1 (header of the first column) and I use
this to set
the filter range Range("A1").CurrentRegion (Use Ctrl * with A1 selected to
see the filter range)
You can also use another cell then A1 in your table but I like to use the
top left cell of the filter range that
is also the header of the first column.

If you want to sort the worksheets in your workbook then go to Chip
Pearson's webpage for a example
http://www.cpearson.com/excel/sortws.htm


Sub Copy_With_AdvancedFilter_To_Worksheets()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Set ws1 = Sheets("Sheet1") '<<< Change
'Tip : You can also use a Dynamic range name,
'http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'This example filter on the first column in the range (change this
if needed)
'You see that the last two columns of the worksheet are used
'to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the
columns)

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False
WSNew.Columns.AutoFit
Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

That does the hard part. Most of the other can be gotten using the macro
recorder as Don suggested.