current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am ultimately trying to export certain sheets in my excel workbook as pdf's.

I have all the names of the sheets I want to export in a named range (in a column). As an example I have "Total" in A1 and "Total (P)" in A2 on the "Data_Mappings" tab. And these two cells have a named range of "Incurred_Graphs".

Here is my current code:

Dim wb As Workbook
Dim TabsArray() As Variant

fp = "C:\Users\chris\Documents\Testing\Graphs\Graphs.pdf"
Set wb = ActiveWorkbook


TabsArray = Worksheets("Data_Mappings").Range("Incurred_Graphs")

'TabsArray = Array("Total", "Total (P)")

wb.Sheets(TabsArray).Select

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fp, _
    Quality:=x1QualityStandad, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

I get a type mismatch on the "TabsArray =" line.

If I change the tabs array to commented out line, everything will work, and I will get my pdf of those 2 tabs.

I eventually would like to pick the named range to use in the array based on a single cell from another sheet. Where the user has a dropdown and can pick for example, "Incurred Graphs" or "Paid Graphs". But I want to get this first part down without having to hardcode in all the names of the sheets into VBA.

Thanks!

share|improve this question

3 Answers 3

up vote 0 down vote

How about this:

Dim wb As Workbook
Dim TabsArray() As Variant

fp = "C:\Users\chris\Documents\Testing\Graphs\Graphs.pdf"
Set wb = ActiveWorkbook

wb.Sheets("Data_Mappings").Range("Incurred_Graphs").Select

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fp, _
    Quality:=x1QualityStandad, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Now I'm not sure if you want to export selection or the whole sheet. If only selections you would change the ActiveSheet.ExportAsFixedFormat with Selection.ExportAsFixedFormat

share|improve this answer
    
Gave me a run-time error '1004': Select method of Range class failed. (I also have a selected print range set on each tab so I can just print each sheet) –  Sqwirtle Mar 3 at 22:10
    
Strange. What if you try wb.Sheets("Data_Mappings").Select and then ActiveSheet.Range("Incurred_Graphs").Select. On which line does it fail them? –  Mitja Bezenšek Mar 3 at 22:54
up vote 0 down vote

You should first activate the Data_Mappings sheet First. Then select the range .U cant directly select the range from another sheet.

Dim wb As Workbook
Dim TabsArray() As Variant

fp = "C:\Users\chris\Documents\Testing\Graphs\Graphs.pdf"
Set wb = ActiveWorkbook
wb.Sheets("Data_Mappings").select
Range("Incurred_Graphs").Select
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fp, _
    Quality:=x1QualityStandad, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
share|improve this answer
    
This will just pdf the "Data_Mappings" sheet. I am looking to use the range from "Data_Mappings" to export each tab labeled in this range. –  Sqwirtle Mar 4 at 12:33
up vote 0 down vote accepted

Here is the modified code that works exactly how I needed it too.

Sub pdf()

Dim wb As Workbook
Dim TabsArray() As Variant
Dim TabsArrayRng As Range

Set wb = ActiveWorkbook
fp = "C:\Users\chris\Documents\Testing\Graphs\" & Sheets("Export to PDF").Range("D7").Text & ".pdf"

Set TabsArrayRng = Sheets("Data_Mappings").Range("Incurred_Graphs")

N = TabsArrayRng.Rows.Count
ReDim TabsArray(1 To N)
For i = 1 To N
    TabsArray(i) = TabsArrayRng.Cells(i, 1).Value2
Next i

wb.Sheets(TabsArray).Select

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fp, _
    Quality:=x1QualityStandad, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False


End Sub

I couldn't directly set the range to the array but needed to use a for/next loop to place each part of the array in.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.