View Full Version : [SOLVED:] pdf print from multiple sheet
gunanidhi30
11-30-2013, 12:34 AM
Hello to the forum member
I have attached one sheet where a macro split the data from main sheet ("Data1") into four different sheets (pfizer, astrazeneca etc)
Is it possible to develop a macro which will randomly print excel data from all splitted sheet into PDF format with print range(A:N).
I have entered the header, footer in the "Data1" which are in different language. Is it possible that the developed macro will automatically capture the data from header and footer and print it.
[[Font size of header=16 and footer is default. header should print system date if possible]]
Thanks for your help in advance.
mancubus
11-30-2013, 09:07 AM
hi.
welcome to VBAX.
try this.
Sub CreateSheetsFromUniqueValzInCol()
Dim cll As Range
Dim UqLst As String
Dim UqArr
Dim i As Long
Application.DisplayAlerts = False
On Error Resume Next
For Each cll In Sheets("Data1").Columns(1).SpecialCells(2).Offset(1)
If InStr(UqLst, cll.Value) = 0 Then UqLst = UqLst & "|" & cll.Value
Next
UqArr = split(Mid(UqLst, 2), "|")
For i = LBound(UqArr) To UBound(UqArr)
Sheets(UqArr(i)).Delete
Sheets.Add(after:=Sheets(Sheets.Count)).Name = UqArr(i)
Sheets("Data1").Range("A1").AutoFilter 1, UqArr(i)
Sheets("Data1").AutoFilter.Range.Copy Sheets(UqArr(i)).Range("A1")
With Sheets(UqArr(i))
Application.PrintCommunication = False
With .PageSetup
.PrintArea = "$A:$N"
.PrintTitleRows = "$1:$1"
.LeftHeader = ""
.CenterHeader = Sheets("Data1").PageSetup.CenterHeader
.RightHeader = Sheets("Data1").PageSetup.RightHeader
.LeftFooter = Sheets("Data1").PageSetup.LeftFooter
.CenterFooter = ""
.RightFooter = ""
.Orientation = xlLandscape
.FitToPagesWide = 1
End With
Application.PrintCommunication = True
.Columns.AutoFit
.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & UqArr(i) & ".pdf"
End With
Next
End Sub
gunanidhi30
11-30-2013, 09:35 AM
Thanks mancubus for spending your valuable time and looking into my issue.
I am getting compilation error near the "split" function while running the script. as (Wrong number of arguments or invalid property assignment).
can you please check once again
Thanks for your time
gunanidhi30
11-30-2013, 02:09 PM
Finally the macro prints for only one sheet. How can I automate for all sheet.
Please check the attachment
Sub pdf()
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 14).End(xlUp).Row
lc = Cells(Columns.Count, 14).End(xlToLeft).Column
With ActiveSheet.PageSetup
.PrintArea = "$A:$N"
.PrintTitleRows = "$1:$1"
.LeftHeader = ""
.CenterHeader = Sheets("Data1").PageSetup.CenterHeader
.RightHeader = Sheets("Data1").PageSetup.RightHeader
.LeftFooter = Sheets("Data1").PageSetup.LeftFooter
.CenterFooter = ""
.RightFooter = ""
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"a.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
mancubus
11-30-2013, 02:22 PM
this code works for me:
there are no major changes...
Sub CreateSheetsFromUniqueValzInColSaveAsPDF()
Dim cll As Range
Dim UqLst As String
Dim UqArr
Dim i As Long
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
On Error Resume Next
For Each cll In Sheets("Data1").Columns(1).SpecialCells(2).Offset(1)
If InStr(UqLst, cll.Value) = 0 Then UqLst = UqLst & "|" & cll.Value
Next
UqArr = split(Mid(UqLst, 2), "|")
For i = LBound(UqArr) To UBound(UqArr)
Sheets(UqArr(i)).Delete
Sheets.Add(after:=Sheets(Sheets.Count)).Name = UqArr(i)
With Sheets("Data1")
.Range("A1").AutoFilter 1, UqArr(i)
.AutoFilter.Range.Copy Sheets(UqArr(i)).Range("A1")
End With
With Sheets(UqArr(i))
Application.PrintCommunication = False
With .PageSetup
.PrintArea = "$A:$N"
.PrintTitleRows = "$1:$1"
.LeftHeader = ""
.CenterHeader = Sheets("Data1").PageSetup.CenterHeader
.RightHeader = Sheets("Data1").PageSetup.RightHeader
.LeftFooter = Sheets("Data1").PageSetup.LeftFooter
.CenterFooter = ""
.RightFooter = ""
.Orientation = xlLandscape
.FitToPagesWide = 1
End With
Application.PrintCommunication = True
.Columns.AutoFit
.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & UqArr(i) & ".pdf"
End With
Next
With Sheets("Data1")
.Activate
.ShowAllData
End With
End Sub
gunanidhi30
11-30-2013, 02:44 PM
system date is not populated to the right section of header in PDF file . Is it possible to set only the header center font size to 16.(Default font for footer and other)
Thanks in advance
mancubus
11-30-2013, 04:57 PM
i cant say anything about that. because the code adds date into header when i run it and i can see it in pdf file.
VBAX does not allow me to upload PDF files. but when i open the PDF file created by macro, i can see the current date in header.
.
gunanidhi30
11-30-2013, 10:53 PM
Thank you so much for your great help.:ipray:
mancubus
12-01-2013, 06:49 AM
you are welcome. i'm glad it helped.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.