CODEWITHSUNDEEP
excelvbaloopspdf
Basara
Basara

Reputation: 25

VBA: replace cell value on a sheet from a list then export as PDF

I have a worksheet1 in which there are some fields automatically compiled based on a value from another worksheet2.

What I want to do is:

  1. Set the value from worksheet2 into the cell of worksheet1.
  2. Export worksheet1 as a PDF
  3. Then, repeat for the next value in the list of worksheet2

Here is my solution for step 1 and step 2:

Sub PrintSameSheetMultipleTimes()
    Dim i As Long
    For i = 1 to 10
      Worksheets("worksheet1").Range("A1").Value = Worksheets("worksheet2").Range("A" & i).Value
      ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & Application.PathSeparator & Worksheets("worksheet2").Range("A" & i) & ".pdf", xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterpublish:=False
    Next i

    MsgBox "Your PDF files has been exported.", vbOKOnly + vbInformation, "PDF Export"
End Sub

Somehow step 3 doesn't work and I end up with only one PDF which contains the first value for i=1. Anything after this isn't exported.

My first question: how can I export sheets with value i= 1 to 10 as PDFs?

My second question: is it even possible to export all generated PDFs (i= 1 to 10) into one PDF?

Upvotes: 0

Views: 190

Answers (1)

SzB
SzB

Reputation: 66

Private Sub CommandButton1_Click()
Dim outapp As Outlook.Application
Dim outmail As Outlook.MailItem

'referanstan outlook seçmeyi unutma

For i = 4 To 13
Range("O6") = Sheets("Sayfa1").Cells(i, 1)
Set outapp = New Outlook.Application
With ActiveSheet.PageSetup
.PrintArea = "$D$8:$E$9"
.FitToPagesWide = 1
.Orientation = xlLandscape

End With

gecicipath = ThisWorkbook.Path & "\"
pdffile = gecicipath & Range("D9") & ".pdf"
Set alan = Range(ActiveSheet.PageSetup.PrintArea)
alan.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdffile, openafterpublish:=False
Set outmail = outapp.CreateItem(olMailItem)
With outmail
.To = Range("I10")
.Subject = "Nerede yaşıyorsun"

.Body = "Sayın" & Range("D9") & vbCrLf & "Nerede yaşadığınız ektedir."
.Attachments.Add pdffile
.Send

End With
Set outmail = Nothing
Set outapp = Nothing
Kill pdffile

Next i

End Sub

With this code, I was sending the payroll PDF to everyone in my company on time and sending them via e-mail. You can create pdf for everyone on page1 using the for loop with dweb. how you can collect them all in 1 pdf i have no idea i hope i helped

Upvotes: 1

Related Questions