PDA

View Full Version : [SOLVED] Separate files and save them in PDF format



Romulo Avila
03-22-2017, 06:00 PM
Good evening,
I have a spreadsheet with several data and I need to separate by column H (Con_Emp) so that each file is saved in the formed PDF in the "C: \ Check" folder, and also the column D (Value) is totalized.
If possible, the file is saved with the constant name in column H.


I thank the attention.


** Spreadsheet attached

mdmackillop
03-22-2017, 07:22 PM
Give this a try

Option Explicit
Sub Test()
Dim dict, d
Dim Rng As Range, cel As Range, c As Range
Dim Dest As String
Dim sh As Worksheet

Application.ScreenUpdating = False
Set sh = Sheets("Plan1")
Set dict = CreateObject("Scripting.Dictionary")
Set Rng = Range(sh.Cells(2, 8), sh.Cells(Rows.Count, 8).End(xlUp))
Set c = sh.Cells(Rows.Count, 4).End(xlUp)(2)

Dest = "C:\Check\"

'Create unique list
On Error Resume Next
For Each cel In Rng
dict.Add cel.Value, cel.Value
Next
On Error GoTo 0

'Filter & Print
For Each d In dict.items
sh.Range("$H:$H").AutoFilter Field:=1, Criteria1:=d
Set Rng = Range(Cells(2, 4), c.Offset(-1)).SpecialCells(xlCellTypeVisible)
c.Formula = "=SUM(" & Rng.Address & ")"
sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Dest & d & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
Next
'Remove filter
sh.Range("$H:$H").AutoFilter
c.ClearContents
Application.ScreenUpdating = True
End Sub

Romulo Avila
03-22-2017, 08:22 PM
Mdmackillop,

Good Morning,
The totalization appears only in the first PDF file, the others are not appearing.
Thank you

mdmackillop
03-23-2017, 03:37 AM
I don't have that issue, Step through the code and check the total is there.
Try adding a pause loop prior to the Export code; remember to Dim i

c.Formula = "=SUM(" & Rng.Address & ")"
For i = 1 To 1000
DoEvents
Next i

Romulo Avila
03-23-2017, 07:04 AM
Good Morning,

It worked, thank you very much for the help.