Hi,
I created macro for PRINT and SAVE IN PDF FORMAT. If i click the PRINT button in excel it goes automatcally to the printer. But the problem is it pull up the complete rows to the printers. I just want to take printout which ever rows are filled. Say example: If i have first 50 rows filled, if i click print, its showing almost 30,000 rows from the excel in the printers.
I created another macro for SAVE in PDF FORMAT. I am getting same error in that as well. It pull up almost 30,000 rows in pdf format.
Please check the below code,
For PRINT
Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim msg As String
Dim i As Integer
Sheets("TPM QRQC").Activate
If StartRow > EndRow Then
msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
MsgBox msg, vbCritical, APPNAME
End If
ActiveSheet.PrintPreview
ActiveSheet.PrintOut
End Sub
NEXT ONE: FOR SAVE IN PDF
Sub SaveToPDF()
'
' SaveToPDF Macro
' Saves Worksheet to PDF
'
'
Dim strFileName As String, strC3 As String, strWorksheet As String
'Set Variable Values
strC3 = Range("C3").Value
strWorksheet = ActiveSheet.Name
strFileName = strC3 & " " & strWorksheet
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"U:\" & strFileName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub
Thanks
Macro Problem
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro Problem
Try the following versions of the macros:
The macros attempt to set the Print Area of the worksheet to the used rows in columns A to L. If you want to print more or fewer columns, change the letter L in the line
Code: Select all
Sub PrintForms()
Dim m As Long
Sheets("TPM QRQC").Activate
m = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PrintArea = "$A$1:$L$" & m
ActiveSheet.PrintPreview
ActiveSheet.PrintOut
End Sub
Sub SaveToPDF()
'
' SaveToPDF Macro
' Saves Worksheet to PDF
'
'
Dim strFileName As String, strC3 As String, strWorksheet As String
Dim m As Long
'Set Variable Values
strC3 = Range("C3").Value
strWorksheet = ActiveSheet.Name
strFileName = strC3 & " " & strWorksheet
m = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PrintArea = "$A$1:$L$" & m
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:="U:\" & strFileName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
Code: Select all
ActiveSheet.PrintArea = "$A$1:$L$" & m
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Macro Problem
Thanks for your kind help hans,
But i am getting error in
ActiveSheet.PrintArea = "$A$1:$L$" & m
But i am getting error in
ActiveSheet.PrintArea = "$A$1:$L$" & m
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro Problem
Sorry, my mistake. Change the line
to
Code: Select all
ActiveSheet.PrintArea = "$A$1:$L$" & m
Code: Select all
ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & m
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Macro Problem
Brilliant work . Thanks a lot