| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Report is such a pain!
Thanks...you are amazing!
"Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Tiff" wrote: That is correct ... "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Tiff" wrote: 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... "Jim Thomlinson" wrote: 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? -- HTH... Jim Thomlinson "Tiff" wrote: 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! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change a crostab report to a simple columnar format | Excel Discussion (Misc queries) | |||
| Oh God the Pain! Help Please! | Excel Discussion (Misc queries) | |||
| Simple report from data list | Excel Programming | |||
| Simple Excel report | Excel Discussion (Misc queries) | |||
| Need simple good expense report in excel | Excel Discussion (Misc queries) | |||
Threaded Mode
