Need help for conversion

[abraham30]

Dear All,

In the attached spreadsheet, I want to convert the output into pdf format using VBA (plz chk pdf sheet). Please note that the both rows and columns are of variable range.

The macro will automatically capture both variable rows and columns and then print into PDF format.

Requirement

Print area- variable rows but column from A:H
Rows to repeat at top:-$1:$8
Footer:--Insert page number/Insert number of pages (Center section)

Advance thanks for your help

[jolivanes]

Variable Rows and Variable Columns!
Variable Columns, can this be less then Column H?
What was the result when you tried the macro recorder? What do you want to change in the result from the macro recorder?

For the variable rows you can use



Dim lr As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row '<---- unless there are less columns used
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$H$" & lr '<---- unless there are less columns used
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\PDF Documents\cdfg.pdf", Quality:=xlQualityStandard, _ '<---- Change folder to save to
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

[abraham30]

Thanks joly. Its working perfectly.

[abraham30]

If column is in variable range, can I use like this.
I am getting some error while running


Dim lr As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row '<---- unless there are less columns used
lc = Cells(Columns.Count, 8).End(xlToLeft).Column
With ActiveSheet.PageSetup
.PrintArea = lc & lr '<---- unless there are less columns used
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\DASHAS2\Desktop\cdfg.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True


For few sheet, I am missing few columns while generating pdf. Could u plz help me once again

[jolivanes]

Maybe this will work in your case.


Sub Save_As_PDF_C()
With ActiveSheet.PageSetup
.PrintArea = "=" & ActiveSheet.UsedRange.Address
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = 100 '<----- Change as required
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\PDF Documents\cdfg.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True
End Sub

[abraham30]

Thank Jolly for spending your valuable time with my query.

I have attached one sheet where I have used the latest code u have provided.
It prints some unnecessary page which is not required.

Advance thanks for your help

[jolivanes]

You have a big range that is not "empty".
Press F5, select "Special" and select "Last Cell".
This will select somewhere around row 36,000.
Select the rows from here to just below the last row in your visible used range and delete all these rows.
Note: Select rows and not cells to delete.

If you can't get that done, let us know and we'll get other code.

[abraham30]

Hello Joli,

This page is automatically generated from another macro code. I can not define the range. Is it possible that if another macro is added to the previous one, then pdf is also generated from the old macro output.

Please chk the macro in attached spreadsheet.


Advance thanks

[jolivanes]

Give this a try

Sub Print_To_PDF_D()
Dim myLastRow As Long, myLastColumn As Long, RTP As String
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [XFD1], , , xlByColumns, xlPrevious).Column
RTP = Range(Cells(1, 1), Cells(myLastRow, myLastColumn)).Address
With ActiveSheet.PageSetup
.PrintArea = RTP
.PrintTitleRows = "$1:$8"
.CenterFooter = "&P/&N"
.PrintQuality = 600
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\DASHAS2\Desktop\AST.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True
End Sub

BTW. You can always clear the "empty cells" with code.
In this case is does not look to be a problem but often enough it makes the file size very large if there is formatting involved, or formulae that equate to ""

[abraham30]

Thanks Joly for your attention towards my query.
The issue is still there.

I don't know whether this can not be managed by VBA.

Thanks once again.