Macro to aAutomating report
 

I have a spreadsheet with 3 worksheets. Sheet 1 contains the data which comes
from survey software, Sheet 2 runs calculations and Sheet 3 is the finished
report which is then saved as PDF. To make a report, I copy and paste the row
of data I need into Row 2 of Sheet 1 (which Sheet 2 and 3 reference) and then
publish Sheet 3 to PDF. This works well if there only a few reports to
create, but I now have to do about 1500 (rows 11-1500) and need to automate
the process.
I think I need a macro that will copy Row 5 and paste into Row 2, then
publish Sheet 3 to PDF and save it as €œ[columnC] [columnD] Report€�, then move
onto Row 6, etc..
Hoping someone can help since Im not an expert with coding.
Thanks in advance!

Macro to aAutomating report
 

turn on the mqacro record while doing one reprot. Then post the code from
the recorder. It is easier to start from a prerecorder macro then to start
from scrtch.

"GeorgeA" wrote:

I have a spreadsheet with 3 worksheets. Sheet 1 contains the data which comes
from survey software, Sheet 2 runs calculations and Sheet 3 is the finished
report which is then saved as PDF. To make a report, I copy and paste the row
of data I need into Row 2 of Sheet 1 (which Sheet 2 and 3 reference) and then
publish Sheet 3 to PDF. This works well if there only a few reports to
create, but I now have to do about 1500 (rows 11-1500) and need to automate
the process.
I think I need a macro that will copy Row 5 and paste into Row 2, then
publish Sheet 3 to PDF and save it as €œ[columnC] [columnD] Report€�, then move
onto Row 6, etc..
Hoping someone can help since Im not an expert with coding.
Thanks in advance!

Macro to aAutomating report
 

This is the macro for creating one report and saving as PDF. Column H is the
email address so when I save the report I want to call it "[email address]
Benefits Report".
Thanks!

Sub BenefitsReport()
'
' BenefitsReport Macro
'

'
Sheets("Zoomerang Data").Select
Rows("16:16").Select
Selection.Copy
Rows("2:2").Select
ActiveSheet.Paste
Range("H16").Select
Application.CutCopyMode = False
Sheets("Benefits Report").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\George\Documents\M-Link\Calculators\June 2009\TDMP
Benefits Benefits Report.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

"Joel" wrote:

turn on the mqacro record while doing one reprot. Then post the code from
the recorder. It is easier to start from a prerecorder macro then to start
from scrtch.

"GeorgeA" wrote:

I have a spreadsheet with 3 worksheets. Sheet 1 contains the data which comes
from survey software, Sheet 2 runs calculations and Sheet 3 is the finished
report which is then saved as PDF. To make a report, I copy and paste the row
of data I need into Row 2 of Sheet 1 (which Sheet 2 and 3 reference) and then
publish Sheet 3 to PDF. This works well if there only a few reports to
create, but I now have to do about 1500 (rows 11-1500) and need to automate
the process.
I think I need a macro that will copy Row 5 and paste into Row 2, then
publish Sheet 3 to PDF and save it as €œ[columnC] [columnD] Report€�, then move
onto Row 6, etc..
Hoping someone can help since Im not an expert with coding.
Thanks in advance!

Macro to aAutomating report
 

Try the below (untested)...Basically there is a loop placed to copy each row
to row2 and then execute print....check the export code for any syntax error/
missing space etc;;;

Sub BenefitsReport()
Sheets("Benefits Report").Select

For lngRow = 5 To 1500
Sheets("Zoomerang Data").Rows(lngRow).Copy _
Sheets("Zoomerang Data").Rows(2)

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\George\Documents\M-Link\Calculators\June 2009\TDMP" & _
" Benefits Report\" & Range("c" & lngRow) & Range("d" & lngRow) & _
" Benefits Report.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"GeorgeA" wrote:

This is the macro for creating one report and saving as PDF. Column H is the
email address so when I save the report I want to call it "[email address]
Benefits Report".
Thanks!

Sub BenefitsReport()
'
' BenefitsReport Macro
'

'
Sheets("Zoomerang Data").Select
Rows("16:16").Select
Selection.Copy
Rows("2:2").Select
ActiveSheet.Paste
Range("H16").Select
Application.CutCopyMode = False
Sheets("Benefits Report").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\George\Documents\M-Link\Calculators\June 2009\TDMP
Benefits Benefits Report.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

"Joel" wrote:

turn on the mqacro record while doing one reprot. Then post the code from
the recorder. It is easier to start from a prerecorder macro then to start
from scrtch.

"GeorgeA" wrote:

I have a spreadsheet with 3 worksheets. Sheet 1 contains the data which comes
from survey software, Sheet 2 runs calculations and Sheet 3 is the finished
report which is then saved as PDF. To make a report, I copy and paste the row
of data I need into Row 2 of Sheet 1 (which Sheet 2 and 3 reference) and then
publish Sheet 3 to PDF. This works well if there only a few reports to
create, but I now have to do about 1500 (rows 11-1500) and need to automate
the process.
I think I need a macro that will copy Row 5 and paste into Row 2, then
publish Sheet 3 to PDF and save it as €œ[columnC] [columnD] Report€�, then move
onto Row 6, etc..
Hoping someone can help since Im not an expert with coding.
Thanks in advance!

Macro to aAutomating report
 

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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

Macro to aAutomating report
 

I really appreciate this and the macros do work and loop but the problem is
that they overwrite the report every time because it always has the same
filename. Column H in the Zoomerang Data sheet is the email address which is
unique. Is it possible to have the filename of the report created be named
"[emailaddress] Benefits Report.pdf"

Thanks again.

"Joel" wrote:

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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

Macro to aAutomating report
 

The filename is specified here

Filename:=Folder & _
..Range("C" & RowCount) & _
..Range("D" & RowCount) & _
FName, _


You can make whatever changes you need like this

Filename:=Folder & _
..Range("C" & RowCount) & _
..Range("D" & RowCount) & _
..Range("H" & RowCount) & _
FName, _


"GeorgeA" wrote:

I really appreciate this and the macros do work and loop but the problem is
that they overwrite the report every time because it always has the same
filename. Column H in the Zoomerang Data sheet is the email address which is
unique. Is it possible to have the filename of the report created be named
"[emailaddress] Benefits Report.pdf"

Thanks again.

"Joel" wrote:

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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

Macro to aAutomating report
 

Joel, I see what you mean about the filename, but the column data I want to
add to the filename is Column H from the Zoomerang Data sheet, not the
Benefits Report sheet.

I keep getting a debug error and this section is highlighted in yellow:

Sheets("Benefits Report").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


"Joel" wrote:

The filename is specified here

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _


You can make whatever changes you need like this

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
.Range("H" & RowCount) & _
FName, _


"GeorgeA" wrote:

I really appreciate this and the macros do work and loop but the problem is
that they overwrite the report every time because it always has the same
filename. Column H in the Zoomerang Data sheet is the email address which is
unique. Is it possible to have the filename of the report created be named
"[emailaddress] Benefits Report.pdf"

Thanks again.

"Joel" wrote:

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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

Macro to aAutomating report
 

the DOT in front of these lines : .Range("C" & RowCount) indicates to use the
property in the WITH statement : With ZoomSht

What error are you getting when the line is highlighted in YELLOW. I don't
have excel 2007 installed on the PC I'm using and the export method isn't
supported in 2003.

I made a couple of minor changes in the code below to see if this solves the
problem. I changed the FOLDER line which was too long. Made two lines. I
also change the export filename to match yoiu original request of having the
email the first item in the filename.

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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




"GeorgeA" wrote:

Joel, I see what you mean about the filename, but the column data I want to
add to the filename is Column H from the Zoomerang Data sheet, not the
Benefits Report sheet.

I keep getting a debug error and this section is highlighted in yellow:

Sheets("Benefits Report").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


"Joel" wrote:

The filename is specified here

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _


You can make whatever changes you need like this

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
.Range("H" & RowCount) & _
FName, _


"GeorgeA" wrote:

I really appreciate this and the macros do work and loop but the problem is
that they overwrite the report every time because it always has the same
filename. Column H in the Zoomerang Data sheet is the email address which is
unique. Is it possible to have the filename of the report created be named
"[emailaddress] Benefits Report.pdf"

Thanks again.

"Joel" wrote:

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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

Macro to aAutomating report
 

That works perfectly, thank-you very much!

"Joel" wrote:

the DOT in front of these lines : .Range("C" & RowCount) indicates to use the
property in the WITH statement : With ZoomSht

What error are you getting when the line is highlighted in YELLOW. I don't
have excel 2007 installed on the PC I'm using and the export method isn't
supported in 2003.

I made a couple of minor changes in the code below to see if this solves the
problem. I changed the FOLDER line which was too long. Made two lines. I
also change the export filename to match yoiu original request of having the
email the first item in the filename.

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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




"GeorgeA" wrote:

Joel, I see what you mean about the filename, but the column data I want to
add to the filename is Column H from the Zoomerang Data sheet, not the
Benefits Report sheet.

I keep getting a debug error and this section is highlighted in yellow:

Sheets("Benefits Report").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


"Joel" wrote:

The filename is specified here

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
FName, _


You can make whatever changes you need like this

Filename:=Folder & _
.Range("C" & RowCount) & _
.Range("D" & RowCount) & _
.Range("H" & RowCount) & _
FName, _


"GeorgeA" wrote:

I really appreciate this and the macros do work and loop but the problem is
that they overwrite the report every time because it always has the same
filename. Column H in the Zoomerang Data sheet is the email address which is
unique. Is it possible to have the filename of the report created be named
"[emailaddress] Benefits Report.pdf"

Thanks again.

"Joel" wrote:

Jacob's code didn't start at row 11 and had a fixed last row of row 15. I
made the last row variable based on the data in the worksheet

Sub BenefitsReport()
'
' BenefitsReport Macro
'

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