• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

macro to select sheets and save as pdf

I have the following macro that saves selected sheets as a PDF file. This works fine, But what I would like to do is that it only selects the sheets depending if they have data in cell C4 on that sheet.  eg:
                       Sheets(Array((if C4="something" then "Sht 1 - Table 1"), (if C4="something" then "Sht 2 - Table 1"), (if C4="something" then "Sht 3 - Table 1"), _
        (if C4="something" then "Sht 4 - Table 1"))).Select
 
Then where it saves the file below as "Test pdf 2.pdf" I would like the file name to be saved as C2 on sheet "sht 1 - Table 1" (this is the job name that the user entered in C2.

I am just not sure about how to write code to do this on the below macro.


Sub Macro6()
'
' Macro6 Macro
'
Sheets(Array("Sht 1 - Table 1", "Sht 2 - Table 1", "Sht 3 - Table 1", _
        "Sht 4 - Table 1", "Sht 5 - Table 1", "Sht 6 - Table 1", _
         "Sht 7 - Table 1", "Sht 8 - Table 1")).Select
    Sheets("Sht 1 - Table 1").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Rob\Documents\NWCI\Programes\Test pdf 2.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

Thanks

Rob
0
RobJanine
Asked:
RobJanine
  • 3
  • 3
1 Solution
 
AndibevanCommented:
Here you go:-

Sub SaveSheetsAsPDF()
  Dim ws As Worksheet
  Dim sSheet As Worksheet
  Dim SheetsFound()
  ReDim SheetsFound(0)
    For Each sSheet In ActiveWorkbook.Worksheets
        If Len(sSheet.Range("C4").Value) > 0 Then
            'Debug.Print sSheet.Name & " has data"
            SheetsFound(UBound(SheetsFound)) = sSheet.Name
            ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
        End If
    Next sSheet
    Debug.Print VarType(SheetsFound)
 ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
  Sheets(SheetsFound).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ActiveWorkbook.Path & "\20111017_macro to select sheets and save as pdf.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

Open in new window

0
 
RobJanineAuthor Commented:
Hi, sorry have been away for the weekend.
I cant seam to get this to work. It is choosing all the sheets in my wookbook and creating a pdf of all sheets. after i close the pdf all sheets are grouped and I have to ungroup. there are other sheets in this workbook other than the 8 above, but even with no value or a 0 value in c4 it seams to be still choosing it.

Thanks
Rob
0
 
AndibevanCommented:
Can you please upload a sample spreadsheet that demonstrates the problem?

I started with a fresh workbook and populated cells Sheet1!C4=test and Sheet3!C4=test.  When I run the macro above only 2 sheets are printed.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
AndibevanCommented:
See attached file for a working example
20111017-macro-to-select-sheets-.xlsm
0
 
RobJanineAuthor Commented:
I have attached the excel workbook. so based on the example it should make a pdf of sht1 through to sht6.
I still cannot make this work.
Sorry I took so long to get back to you....been away again.

Cheers
Rob.
example-pdf.xlsm
0
 
RobJanineAuthor Commented:
Thankyou, I did manage to get it working.

Cheers

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now