Macro to aAutomating report

G

GeorgeA

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 I’m not an expert with coding.
Thanks in advance!
 
J

Joel

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.
 
G

GeorgeA

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 Report\[email protected] Benefits Report.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

Joel said:
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 said:
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 I’m not an expert with coding.
Thanks in advance!
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 Report\[email protected] Benefits Report.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

Joel said:
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 said:
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 I’m not an expert with coding.
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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
 
G

GeorgeA

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.
 
J

Joel

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

GeorgeA

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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 said:
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 said:
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 said:
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

GeorgeA

That works perfectly, thank-you very much!

Joel said:
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 said:
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 said:
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, _


:

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.

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to list report results 3
VBA? Macro? 0
Macro to automatically print & save as PDF 2
How to copy from sheet1 then paste special transpose to sheet2,3,4 1
Macro for averaging 3
Macro for Search 6
Macro problem 2
Excel macro question 7

Top