VB Save as OR print as PDF macro multiple sheets

[toolyfiddler]

Hi guys, I am running '07.

Thankyou for looking at my problem, I always appreciate the help I find on here.

I have searched over the last two days and I can find snippets of code, but I haven't found a combination to make it do exactly what I need, and being a novice, I have so far been unable to help myself.

Might I request, if you do take the time to post some code (appreciated) some commenting would be helpful to help me help myself, and maybe help others in the future

If I modify the code to make it do what I want, I will post the successful code in here. I tend to get a bit annoyed when people have similar problems, take some code someone's posted, modify it then just post back with "Yep modified it, got it to work!" without posting the successful code. That doesn't help anyone....(/rant)

I have a multiple sheet workbook that I want to do the following (Preferably in one macro that I will attach to a button)

-Save sheet one ('Customer') as PDF print range is (A1:B34) to "C:\Documents and Settings\All Users\Desktop\XXX.pdf" XXX should be replaced with cell data from B9 and E28

-Save sheet 2 ('Estimate 1' cell range A1:I59) AND sheet 3 (A1:B45) as two page pdf "C:\Documents and Settings\All Users\Desktop\XXX.pdf" same as above, but with the word "Quote" added to the end of the filename.


All computers that will be using this are XP and run Excel '07
All also have Adobe Acrobat 6 Pro

Extra points for being able to have the file create a path "C:\Documents and Settings\All Users\Desktop\SHW\(Cell Data from B9)\FILENAME AS ABOVE

Thanks everyone!

 

[toolyfiddler]

No Joy?

 

[toolyfiddler]

I made this one and it works a treat!

It does the following:

-Saves two files to PDF
- First File: Sheet1 (Customer) into a PDF using cell range and adds the word "Detail" to the end of the filename (In pdf)
- Second File: Selects Sheet 2 and 3 and names them using the cell range from sheet 1, and adds the word "Quote" on the end
- Saves both files to desktop (In XP - you can see I used "All Users")
- Application.ScreenUpdating = False also hides the scren flicking between sheets.
- Only the second file opens up onscreen once finished, the first one does not show up.

Sub SaveAsAuto()
    Application.ScreenUpdating = False
    Sheets("Customer").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\All Users\Desktop\" & Range("E28") & "-Detail", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Sheets(Array("Estimate 1", "Rebate Letter")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Documents and Settings\All Users\Desktop\" & Sheets("Customer").Range("E28") & "-Quote", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Sheets("Customer").Select
End Sub

 

[toolyfiddler]

I forgot to mention, one of the crucial things I found was

Sheets(Array("Estimate 1", "Rebate Letter")).Select

Which is the same as holding down ctrl and selecting multiple sheets (Where Estimate 1 and Rebate Letter are the names of the two sheets I selected)

This means I have one pdf with multiple pages, instead of one pdf per page, which is what I wanted to avoid.



Hope this helps someone!

 

[Joanna74]

This was really interesting to me as I am looking for code to print/convernt multiple worksheets in a large workbook into one PDF file with a page for each report (pivot tables) so that our managers can just push a print buttion or a convert button and get the PDF file and then print it rather than printing each individual sheet manually.

Not sure what anyone may need from me to help me but happy to provide any information necessary. So far have managed button click programming but only been "aware" of the capabilities using VB for the past week - so when I say novice I mean novice lol.

once I can get the basic script I can fill in the required work sheets names and cells etc as long as teh basic structure is there.

Anyway any help be much appreciated.

cheers