Macro to list report results

[GeorgeA]

Hello,
The macro below copies and pastes a row of data from sheet 'Zoomerang Data'
into row 2 and calculations reference that row. The results are formatted
into a report and the macro saves the report in PDF and then copies the next
data row and overwrites Row 2, etc... It works great (Thanks Joel!) but I
have to open each PDF to verify the results.
I have made a new sheet called Results that references the required fields
in the 'Benefits Report' sheet (ie. ='Benefits Report'!D18, etc..). I'd like
to end up with rows of data that show the results of each report, but I get
stuck because the macro below always overwrites the last row the row for
calculations and I don't know how to keep the results from overwriting. I was
trying Paste Values but then I lose the references in the Results fields. Can
someone help me out. Thanks!

Sub BenefitsReport()
'
' BenefitsReport Macro
'

Folder = "C:\Users\Andrew\Documents\M-Link\Benefit Reports\"
FName = " TDMP Benefits Report.pdf"
'
Set ZoomSht = Sheets("Zoomerang Data")
With ZoomSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 11 To 1500
.Rows(RowCount).Copy _
Destination:=.Rows(2)
Sheets("Benefits Report").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Folder & _
.Range("H" & RowCount) & _
FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next RowCount
End With
End Sub

 

[Eduardo]

Hi,
Why you don't have your calculations in the first row instead, and then
change the destination row to 3

 

[GeorgeA]

I can't do this because the calculations are in a separate worksheet. There
are tons of them and they create amortization schedules and all kinds of
results. The Benefits Report sheet simply pulls what it needs from various
fields.

 

[Dave Peterson]

I have no idea what etc means for you data layout, but maybe this would help:

Option Explicit
Sub BenefitsReport()

Dim Folder As String
Dim fName As String
Dim ZoomSht As Worksheet
Dim RptSht As Worksheet
Dim NextRow As Long
Dim LastRow As Long
Dim RowCount As Long

Folder = "C:\Users\Andrew\Documents\M-Link\Benefit Reports\"
fName = " TDMP Benefits Report.pdf"

Set RptSht = Worksheets("Benefits Report")
With RptSht
'assumes column A in the rptsht is always used
'if that row has data on it
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set ZoomSht = Sheets("Zoomerang Data")
With ZoomSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 11 To 1500
.Rows(RowCount).Copy _
Destination:=.Rows(2)

With RptSht.Cells(NextRow, "A")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With

With RptSht.Cells(NextRow, "B")
.Value = Application.UserName
End With

'change the "sending" addresses to what you need
RptSht.Cells(NextRow, "C").Value = .Range("D18").Value
RptSht.Cells(NextRow, "D").Value = .Range("x92").Value
RptSht.Cells(NextRow, "E").Value = .Range("A7").Value

Sheets("Benefits Report").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Folder & _
.Range("H" & RowCount) & _
fName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next RowCount
End With
End Sub