Simple Report is such a pain!

[Guest]

I have a list of 400 records in the first tab with sales
data for 400 reps. I want to repetitiously print a little summary
report (which is on another tab) which gets data for each rep and applies a
few calculations to it. I don't need to create a separate report for each
....just create a report for the first record, print it, and then refresh the
data for the second record, print it, and so on until a page is printed for
each rep... I hope that is clear... Thanks for the help!

 

[Guest]

A few questions...

What do the 400 source data records look like. What kind of information is
in them.

Does 1 rep have more than 1 source record or is it one record per rep so you
will end up with 400 reports.

What kind of calculations do you need to perform?

 

[Guest]

Thanks Jim...

Example of Data:

Key Rep Product 1 Product 2 Product 3 Product 4
3 10K 1 12 0 0
4 15M 18 36 0 0
5 A02 1 3 0 0
6 A03 0 0 0 0
7 A04 103 0 0 0


The report gets the data for the first rep (10K) and applies a simple
promotion multiplier to each of the products he/she sold for the period:

Account: 10K


Total Points
accumulated Promotion Multiplier
Total Points
Advantage 1 1 1
Remo 12 1.25 15
Precise 0 1.5 0
Concise 0 1 0
Total 16


It is very simple.... I need to print one out for each rep...

 

[Guest]

And each line in the Source Data represent a different rep so you will end up
with 400 reports? I just want to make sure we don't have to do an subtotaling
for reps prior to producing the reports...

 

[Guest]

That is correct ...

And each line in the Source Data represent a different rep so you will end up
with 400 reports? I just want to make sure we don't have to do an subtotaling
for reps prior to producing the reports...

 

[Guest]

Sorry about taking so long. I crashed my system... This code takes your list
of reps and places the rep number into Cell A2 on the report sheet and then
prints that sheet. Using Vlookup formulas you should be able to reference
that cell and create your report...

Public Sub MakeReport()
Dim wksSource As Worksheet
Dim wksReport As Worksheet
Dim rngSourceReps As Range
Dim rngReportRep As Range
Dim rngCurrent As Range

'Define the source data
Set wksSource = Sheets("Sheet1") 'source Sheet
With wksSource
Set rngSourceReps = .Range(.Range("B2"), .Cells(.Rows.Count,
"B").End(xlUp))
End With

'Define the report sheet
Set wksReport = Sheets("Sheet2")
Set rngReportRep = wksReport.Range("A2")

'Make the report
For Each rngCurrent In rngSourceReps
rngReportRep.Value = rngCurrent.Value
wksReport.PrintPreview 'Comment this when it works
'wksReport.PrintOut 'UnComment this when it works
Next rngCurrent
End Sub

 

[Guest]

Thanks...you are amazing!

Sorry about taking so long. I crashed my system... This code takes your list
of reps and places the rep number into Cell A2 on the report sheet and then
prints that sheet. Using Vlookup formulas you should be able to reference
that cell and create your report...

Public Sub MakeReport()
Dim wksSource As Worksheet
Dim wksReport As Worksheet
Dim rngSourceReps As Range
Dim rngReportRep As Range
Dim rngCurrent As Range

'Define the source data
Set wksSource = Sheets("Sheet1") 'source Sheet
With wksSource
Set rngSourceReps = .Range(.Range("B2"), .Cells(.Rows.Count,
"B").End(xlUp))
End With

'Define the report sheet
Set wksReport = Sheets("Sheet2")
Set rngReportRep = wksReport.Range("A2")

'Make the report
For Each rngCurrent In rngSourceReps
rngReportRep.Value = rngCurrent.Value
wksReport.PrintPreview 'Comment this when it works
'wksReport.PrintOut 'UnComment this when it works
Next rngCurrent
End Sub