Help with code in excel

[danswear]

Using a spreadsheet that has a list of multiple accounts for different sales reps, I'm utilizing the drop down auto filter. The reps codes can range from 400-700. There aren't reps with all of the codes as of yet, but in the future all numbers are possible. I want the macro to print the commission results for each rep seperatly. I know I can put a print command for each rep code from 400-700, but the problem is that for the 250 codes that aren't being used it prints a blank, which means I would waste a lot of paper. My thought is that there should be some code that just says print the commission for all reps that are on the sheet and thats it. Also, if it is possible I'd like to know if there is a way to write a seperate macro that say the same thing except that it would email the report instead of printing it. I received some help the other day and most of the code below is from the person from tek-tips, mine are the printing ones that begin with Selection. I hope I explained it well. Thanks.




Public Sub PrintCmsn()

Dim wksActive As Worksheet
Set wksActive = ActiveSheet
Dim filAutoFilter As AutoFilter
Dim rngRow As Range
Set filAutoFilter = wksActive.AutoFilter
For Each rngRow In filAutoFilter.Range.Rows
'If row is visible and not part of the header row then print
If Not rngRow.Hidden And rngRow.Row <> 1 Then
Selection.AutoFilter Field:=3, Criteria1:="436"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=3, Criteria1:="456"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Exit For
End If
Next rngRow
End Sub

 

[SkipVought]

Hi,

Just use the Data/Subtotal feature. Make sure your table is SORTED by Account Nbr. Check the Page Break between groups.

NO CODE REQUIRED.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

Hi,

Just use the Data/Subtotal feature. Make sure your table is SORTED by Account Nbr. Check the Page Break between groups.

NO CODE REQUIRED.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Gavona]

As an alternative approach, could you get the info and presentation you want using a pivot table?

If you have Account numbers set up with no Rep then you can hide data with that field = Blank

If you are looking to email the reports to each rep, rather than emailing the whole thing then:
You could set Rep as a page field and Account Number as a column field (maybe with page break at change of Account Number).
Right click on the table and show pages will create a separate sheet for each Rep
Write some VBA to copy each sheet to values before emailing it.
Nothing to stop you having a list of Reps and the email address to send it to.


Gavin