VBA to Save Several Sheets as 1 PDF named from Cell contents

[Luke24]

I have a workbook containing approx 20 sheets and am looking for a VBA solution to save 3 of the sheets, those named

"Venue Flash", "Peer Group One", and "Peer Group Two"

to a single PDF file.

I wish the name of the new PDF file to be the contents of cell A1 on the "Venue Flash" sheet.

A1 contains a drop down list of 65 venue names, when the user selects a new name from the drop down list the report updates to figures relevant to that venue.

So... if possible (and this is where i get really lost with the coding of this query) i would like the macro to cylce through the list of venue names untill the list is exhausted.

The end result would be I have 65 pdf files, one named after each of the venues in my drop down list, each one containing the contents of the 3 nominated sheets within the workbook.

Help on this is greatly appreciated.

Luke

 

[alx7000]

Hi Luke,

The following works on Excel 10 and may work on 07, if it doesn't work for you let me know.

There are two versions, one for pdfing just the current venue and one for pdfing all venues.

If assumes that:
The pdfs are saved into a folder at C:\Documents and Settings\MrExcelTest\
The list of all venues is in a sheet called 'List of venues' starting in A1

Option Explicit

Sub SaveCurrentToPDF()

Dim OriginalSheet As String

OriginalSheet = ActiveSheet.Name

    Sheets(Array("Venue Flash", "Peer Group One", "Peer Group Two")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\MrExcelTest\" & Sheets("Venue Flash").Range("A1").Value & ".pdf"

'Reset location
Sheets(OriginalSheet).Select

End Sub

Sub SaveAllToPDF()

Dim OriginalSheet, OriginalVenue As String
Dim i As Long

OriginalSheet = ActiveSheet.Name
OriginalVenue = Sheets("Venue Flash").Range("A1").Value

For i = 1 To 65

    Sheets("Venue Flash").Range("A1").Value = Sheets("List of venues").Range("A1").Offset(i - 1, 0).Value

    Sheets(Array("Venue Flash", "Peer Group One", "Peer Group Two")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\MrExcelTest\" & Sheets("Venue Flash").Range("A1").Value & ".pdf"

Next i

'Reset location and venue
Sheets(OriginalSheet).Select
Sheets("Venue Flash").Range("A1").Value = OriginalVenue

End Sub

Any questions please let me know.

Cheers,
alx7000.

 

[Luke24]

YOU LIFE SAVER!

I had to modify slightly for my own needs but it worked perfectly.

thanks heaps

Luke

 

[Gulvafslibning]

YOU DOUBLE LIFE SAVER!

You have now saved two lifes in the recent 2 years! You're fantastic!

THAANKS!

 

[Gulvafslibning]

Just wanted to state for whomever might encounter the same problem as I when using the above code to save pdf files:


Problem: You get 2 errors constantly. Error 400 and error about the file you want to save is read-only.

When: It happens when you use win7 or whatever that has preview of files. So if you left click (not even opening the file) the pdf you just saved, it will be marked as opened for the rest of the time being, unless you close the window. It is still open even though you click other files. Only the below noob solution worked for me.

My noob solution: Just close down the folder you viewed the file from and it can be saved again.

 

[Euchron]

Hello! this worked for me just fine.

Just recently i created this excel report format that hides specific sheets if I choose a specific "purpose".

For illustration, I have a total of 20 sheets.
If i select purpose 1, sheets 1 - 5 are the only ones unhidden while the rest are hidden.
if i select purpose 2, sheets 6 - 10 are unhidden while the rest are hidden
if i select purpose 3, sheets 11 - 15 are unhidden while the rest are hidden....and so forth.

What's the code that only save as pdf the ones that are unhidden and ignores the rest that are hidden?