[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

macro to select sheets and save as pdf

Posted on 2011-10-16
6
Medium Priority
?
435 Views
Last Modified: 2012-06-27
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
Comment
Question by:RobJanine
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:Andibevan
ID: 36977584
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
 

Author Comment

by:RobJanine
ID: 36983814
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
 
LVL 3

Expert Comment

by:Andibevan
ID: 36989311
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Accepted Solution

by:
Andibevan earned 2000 total points
ID: 36989332
See attached file for a working example
20111017-macro-to-select-sheets-.xlsm
0
 

Author Comment

by:RobJanine
ID: 37015851
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
 

Author Closing Comment

by:RobJanine
ID: 37021851
Thankyou, I did manage to get it working.

Cheers

Rob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question