VBA macro for REPLACE cell value from a list THEN print in a single PDF Document!

[INOpportuno]

I found in this forum how to printout sheets with different values, but I'd like to print all the sheets in a single PDF Document.
How can I fix this VBA code to do this?

Sub PrintAll()


Dim i As Long, LastRow As Long


LastRow = Worksheets("Names").Range("A65536").End(xlUp).Row


For i = 1 To LastRow
Worksheets("Form").Range("A1").Value = Worksheets("Names").Range("A" & i).Value
Worksheets("Form").Range("Print_Me").PrintOut
Next i


End Sub

 

[OaklandJim]

You want to print all worksheets in a workbook to a single pdf?

 

[jolivanes]

You asked for all sheets to be saved as a single PDF file. This will do that.
File will be saved in the same Folder where the excel workbook that has the macro in it is saved.
Make sure that this workbook was indeed saved before running the macro.
Name of the PDF file will be "INOpportuno At MrExcel. PDF"

Sub Maybe()
    Dim SheetNames() As String
    ReDim SheetNames(1 To 1)
    Dim ws As Worksheet
    For Each ws In Worksheets
        SheetNames(UBound(SheetNames)) = ws.Name
        ReDim Preserve SheetNames(1 To UBound(SheetNames) + 1)
    Next ws
    ReDim Preserve SheetNames(1 To UBound(SheetNames) - 1)
    Sheets(SheetNames).Select
    ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "INOpportuno At MrExcel"  & ".pdf"
End Sub

Of course it would help if we knew if the print range needed to be set.

 

[INOpportuno]

You asked for all sheets to be saved as a single PDF file. This will do that.
File will be saved in the same Folder where the excel workbook that has the macro in it is saved.
Make sure that this workbook was indeed saved before running the macro.
Name of the PDF file will be "INOpportuno At MrExcel. PDF"

Sub Maybe()
    Dim SheetNames() As String
    ReDim SheetNames(1 To 1)
    Dim ws As Worksheet
    For Each ws In Worksheets
        SheetNames(UBound(SheetNames)) = ws.Name
        ReDim Preserve SheetNames(1 To UBound(SheetNames) + 1)
    Next ws
    ReDim Preserve SheetNames(1 To UBound(SheetNames) - 1)
    Sheets(SheetNames).Select
    ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "INOpportuno At MrExcel"  & ".pdf"
End Sub

Of course it would help if we knew if the print range needed to be set.

Can I combine this code with the other one I sent?

 

[INOpportuno]

You want to print all worksheets in a workbook to a single pdf?

Yes, but I have only one sheet that changes with a value in a cell of a list

 

[jolivanes]

Re: "Can I combine this code with the other one I sent?"

Make an effort in explaining what you want.
It looks like you want to change Range A1 in Forms sheet with values from Column A in Names sheet, print a range set by a NamedRange (Print_Me) and do it all over with the next value.
Is that right?

 

[INOpportuno]

Exactly, now I can print out all the sheets but the program asks me every time to save a single sheet with a new name.
I'd like to print all the sheets automatically in one file pdf.

 

[jolivanes]

Try this. Check and change references like sheet names if required.

Sub Try_So()
Dim a As String, shts, ws As Worksheet, prntNames, i As Long
a = ActiveSheet.Name
prntNames = Sheets("Names").Range("A1:A" & Sheets("Names").Cells(Rows.Count, 1).End(xlUp).Row).Value
Worksheets("Form").PageSetup.PrintArea = Range("Print_Me").Address
    For i = LBound(prntNames) To UBound(prntNames)
        With Sheets("Form")
            .Range("A1").Value = prntNames(i, 1)
            .PrintOut
        End With
    Next i
    For Each ws In ThisWorkbook.Worksheets
        shts = shts & "," & ws.Name
    Next ws
Sheets(Split(Mid(shts, 2), ",")).Select
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "INOpportuno At MrExcel" & ".pdf"
Sheets(a).Select
End Sub

 

[INOpportuno]

Try this. Check and change references like sheet names if required.

Sub Try_So()
Dim a As String, shts, ws As Worksheet, prntNames, i As Long
a = ActiveSheet.Name
prntNames = Sheets("Names").Range("A1:A" & Sheets("Names").Cells(Rows.Count, 1).End(xlUp).Row).Value
Worksheets("Form").PageSetup.PrintArea = Range("Print_Me").Address
    For i = LBound(prntNames) To UBound(prntNames)
        With Sheets("Form")
            .Range("A1").Value = prntNames(i, 1)
            .PrintOut
        End With
    Next i
    For Each ws In ThisWorkbook.Worksheets
        shts = shts & "," & ws.Name
    Next ws
Sheets(Split(Mid(shts, 2), ",")).Select
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "INOpportuno At MrExcel" & ".pdf"
Sheets(a).Select
End Sub

It's the same, It prints 15 different pdf and one more with 2 sheets.
I have one sheet who generates different value based on different value of one cell (list).
I'm not good, maybe I do some mistakes...
I need to generate one Pdf with all these sheets based on one sheet.

 

[jolivanes]

Change your active printer to a line printer.

"with all these sheets based on one sheet"
This is new.

Last kick at the cat.
Explain, as previously asked, what you want. You never said that you wanted to save all these sheets as PDF.
In your first post, it just says Printout which means to make a hardcopy on the printer. Certainly mention which printer everything has to go to.