Macro Problem

[krishnaa_kumarr88]

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

[HansV]

Try the following versions of the macros:

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

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

    ActiveSheet.PrintArea = "$A$1:$L$" & m

[krishnaa_kumarr88]

Thanks for your kind help hans,
But i am getting error in
ActiveSheet.PrintArea = "$A$1:$L$" & m

[HansV]

What is the value of m when you get the error?

[HansV]

Sorry, my mistake. Change the line

Code: Select all

    ActiveSheet.PrintArea = "$A$1:$L$" & m

to

Code: Select all

    ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & m

[krishnaa_kumarr88]

Brilliant work . Thanks a lot