# Excel VBA - Export the same range changed by a function to a PDF file

#### supertitovic

##### New Member
I have tried to find the solution everywhere, but I only find functions to export several sheets in a single PDF.
I want to export the same sheet and the same range, which is changing through a function, in a single PDF file.

For example, in the following code there is a loop that changes the values of a range 3 times.
How can I do to save a single PDF file that contains the range in triplicate and changed?

I have tried using Arrays but I don't know how to implement it in this specific case.

Code:
Dim i As Long
For i = 1 To 3
Call function_to_change_values 'this function change the values of range A1 to D5
'config printing:
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$5" 'print area
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 77
.Draft = False
.PaperSize = xlPaperA4
End With
'create PDF:
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\myfile.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next i
Thank you very much for the help

#### felipemcandido

##### New Member
I don't understand exactly what you want.
So, let's try to help:

1. If you want to create one PDF showing the three transformation, then you need to create 3 ranges (for example, copying A1:D5 to A7:D11 and A13:D17, skip one line to see better the values) and export those after the loop. Your Export is inside the loop.
2. If you want to create more than 1 file with each result, then you need export them changing the filename (for example: FileName := "C:\MyFile" & i & ".pdf").

#### supertitovic

##### New Member
Thank you, but the answer doesn't help me.
What I intend is for a certain range to change its values, print it, change its values again, print it again ... And all that in a single PDF file.
I do not know if I explain myself, since I have found solutions to print different ranges and sheets in a single PDF file, but I have not found a solution for what I specify.

#### felipemcandido

##### New Member
If understand correctly, my answer 1 is almost what you want. After exporting to PDF, clear the another regions, keeping just the first one (original or changed).
If it isn't what you want, paste your expected (or example) result here, so it will be easier to help you.

#### John_w

##### MrExcel MVP
Try this macro, which adds a temporary sheet and copies A1:D5 to the next row, three times. It also puts a page break between each block of cells. Just change the PDFFullName string value to the folder and file name of PDF file you want to create.

Code:
Public Sub Create_PDF()

Dim dataCells As Range
Dim PDFsheet As Worksheet
Dim PDFdestCell As Range, lastRow As Long
Dim PDFFullName As String
Dim i As Long

With ThisWorkbook
Set dataCells = .ActiveSheet.Range("A1:D5")
PDFFullName = "C:\path\to\Data.pdf"     'CHANGE THIS
'Add temporary sheet for PDF data
End With

Set PDFdestCell = PDFsheet.Range("A1")

For i = 1 To 3

Call function_to_change_values 'this function change the values of range A1 to D5

'Copy data cells to temporary sheet

dataCells.Copy PDFdestCell

'Set next destination cell and insert page break in temporary sheet

With PDFsheet
Set PDFdestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
End With

Next

'Set page settings on temporary sheet

Application.PrintCommunication = False
With PDFsheet.PageSetup
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.Zoom = 100
End With
Application.PrintCommunication = True

'Create PDF

PDFsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFullName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

'Delete temporary sheet

PDFsheet.Delete

End Sub

#### supertitovic

##### New Member
Thanks for the answers, but it is not what I am looking for, since the sheet has formats, colors and modifications which have to appear on the printed page.
I already know that you can print different sheets in a single PDF file, as well as do the latter (copy and paste in another new sheet), but, my question is if what I want to do, can it be done through Arrays? Without having to create new sheets.
Thank you and I hope to explain myself well, I use a translator.

#### supertitovic

##### New Member
Explaining it in a simpler way:
Imagine a loop of 1 to 3.
In step 1 a random number is generated in cell A1 and a PDF file of that cell is saved.
In step 2 another random number is generated in cell A1 and another PDF file is saved again.
In step 3 exactly the same.
Can you save in a single PDF instead of saving 3 PDF files? Without having to copy on a different sheet.

#### John_w

##### MrExcel MVP
Thanks for the answers, but it is not what I am looking for, since the sheet has formats, colors and modifications which have to appear on the printed page.
I already know that you can print different sheets in a single PDF file, as well as do the latter (copy and paste in another new sheet), but, my question is if what I want to do, can it be done through Arrays? Without having to create new sheets.
Thank you and I hope to explain myself well, I use a translator.
My code copies the cell values and formats for the specified range to the temporary sheet, so the temporary sheet and therefore the PDF should look exactly like the range on the original source sheet.

No, I don't think it can be done with arrays, since arrays can only store values. How would the array reproduce the visual cell formats? These can only be represented on a sheet.

Explaining it in a simpler way:
Imagine a loop of 1 to 3.
In step 1 a random number is generated in cell A1 and a PDF file of that cell is saved.
In step 2 another random number is generated in cell A1 and another PDF file is saved again.
In step 3 exactly the same.
Can you save in a single PDF instead of saving 3 PDF files? Without having to copy on a different sheet.
You don't have to copy A1 to a different sheet. You could copy A1 3 times (temporarily) to different cells on the same sheet, away from your existing data, e.g. A1001:A1003, and save those as a single PDF. So yes, you could save in a single PDF instead of 3 PDFs.

With your loop above creating 3 PDFs, you could merge (join) these files into a single PDF using an API, for example the Acrobat API. However the Acrobat API is only available to VBA if you install Acrobat Pro, not the free Adobe Reader. There is example code which uses the Acrobat API to merge multiple PDFs at https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1100300-combine-two-pdf-into-one-separate-pdf-save-particular-path-using-vba-post5290362.html#post5290362.

#### DanteAmor

##### Well-known Member
You can reproduce your cell format "$A$1:$D$5" in "$A$6:$D$10" and in "$A$11:$D$15"

Then your code: function_to_change_values, you must update the 3 ranges.

So:
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$15"