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

Luke24

New Member
Joined
Jun 14, 2010
Messages
11
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

alx7000

Board Regular
Joined
Nov 14, 2010
Messages
134
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

Code:
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

New Member
Joined
Jun 14, 2010
Messages
11
YOU LIFE SAVER!

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

thanks heaps

Luke
 

Gulvafslibning

New Member
Joined
Oct 18, 2013
Messages
8
YOU DOUBLE LIFE SAVER!

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

THAANKS!
 

Gulvafslibning

New Member
Joined
Oct 18, 2013
Messages
8
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

New Member
Joined
Mar 24, 2014
Messages
5
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top