VBA Loop to PDF

[smartpat19]

Hi,

I have a list of value in tab "List" A2:A##

I want to update the tab "summary" "A1" with each value from the list which updates the summary Tab with data.

I then want to copy this print area from the tab "summary" to a new workbook until every value in the Tab "List" is used.

I then want to pdf the entire new workbook into one PDF.

Thank you so much for your help! I spend hours doing this task.

Best,
Patrick

 

[DanteAmor]

Try this

The names of the xlsx and pdf files will have as their name the value of the cell in the list sheet.
The files will be saved in the same folder where the book is located with the macro.

Sub Loop_to_PDF()
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, wb As Workbook, wPath As String
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  wPath = ThisWorkbook.Path & "\"
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("summary")
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    sh2.Range("A1").Value = c.Value
    sh2.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs Filename:=wPath & c.Value & ".xlsx"
    wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & c.Value & ".pdf", OpenAfterPublish:=False
    wb.Close False
  Next
  MsgBox "End"
End Sub

 

[smartpat19]

Dante,

Thank you for your help on this, it works great!

Can you make the macro PDF all the sheets together?

So when copying the summary tab, add all the summary tabs into one workbook and then PDF the entire workbook?

Thank you,
patrick

 

[DanteAmor]

Try this

Sub Loop_to_PDF()
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, wb As Workbook, wPath As String
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  wPath = ThisWorkbook.Path & "\"
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("summary")
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    sh2.Range("A1").Value = c.Value
[COLOR=#0000cd]    Sheets.Copy[/COLOR]
    Set wb = ActiveWorkbook
    wb.SaveAs Filename:=wPath & c.Value & ".xlsx"
    wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & c.Value & ".pdf", OpenAfterPublish:=False
    wb.Close False
  Next
  MsgBox "End"
End Sub

 

[smartpat19]

Almost there.

heres an example.

I have 5 values in the list.

I want to update the summary tab with each value so at the end i will have 5 summary tabs in one new file and then PDF that new workbook.

Not sure if that helps.

Thank you for all your help.

 

[DanteAmor]

"La tercera es la vencida"

It is an expression, it almost always works on the third attempt.

Sub Loop_to_PDF()
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, wb As Workbook, wPath As String
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  wPath = ThisWorkbook.Path & "\"
  Set sh1 = Sheets("List")
  Set sh2 = Sheets("summary")
  Set wb = Workbooks.Add
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    sh2.Range("A1").Value = c.Value
    sh2.Copy after:=wb.Sheets(wb.Sheets.Count)
  Next
  wb.Sheets(1).Delete
  wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & "new workbook.pdf", OpenAfterPublish:=False
  wb.Close False
  MsgBox "End"
End Sub

 

[smartpat19]

PERFECTION! thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.