M
Michael
I have the routine below in a number of workbooks. Typically it works
without a problem. I have a sheet with sheet names that I select and
it exports the sheets to a PDF. For some reason sometimes it only
produces a PDF with the first sheet in it instead of all sheetnames
that were selected.
I have made sure the sheetname array has more than one value and have
also made sure (by exiting before the export) that it is selecting
the
correct sheets in a group. Both of those work fine. I have also made
sure that there was not a problem with the SortableDateString
fuction.
If I manually select the sheets (which is a pain in a book of 70 or
more sheets) export works fine - gives me all the sheets I have
selected.
It is like the file is in some state that is making exporting
multiple
sheets a problem. I can open other workbooks with the same exact sub
in it and it will work fine.
Driving me crazy. Must be something too simple to see.
any help would be appreciated!
Sub PDFSet()
Dim c As Variant
Dim sName As String
Dim sh As Worksheet
Dim OrgSheet As String
Dim ExportFileName As String
Dim sheetnames() As String
Dim i As Integer
Dim DefaultFileName As String
i = 0
DefaultFileName = SortableDateString(Now())
OrgSheet = ActiveSheet.Name
ExportFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name &
"." & DefaultFileName & ".pdf")
If ExportFileName = "" Then
'If ExportFileName = False Then ' was causing error so changed to
zero
length string as test
End
End If
On Error GoTo er
For Each c In Selection
If Len(c.Value) > 0 Then
ReDim Preserve sheetnames(i)
sheetnames(i) = c.Value
i = i + 1
End If
Next c
Sheets(sheetnames).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,
Filename:=ExportFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Debug.Print "sheets to print " & UBound(sheetnames)
Sheets(OrgSheet).Select
er:
If err.Number = 9 Then
MsgBox "The Sheet named - " & sName & " - does not exist"
Resume Next
End If
End Sub
without a problem. I have a sheet with sheet names that I select and
it exports the sheets to a PDF. For some reason sometimes it only
produces a PDF with the first sheet in it instead of all sheetnames
that were selected.
I have made sure the sheetname array has more than one value and have
also made sure (by exiting before the export) that it is selecting
the
correct sheets in a group. Both of those work fine. I have also made
sure that there was not a problem with the SortableDateString
fuction.
If I manually select the sheets (which is a pain in a book of 70 or
more sheets) export works fine - gives me all the sheets I have
selected.
It is like the file is in some state that is making exporting
multiple
sheets a problem. I can open other workbooks with the same exact sub
in it and it will work fine.
Driving me crazy. Must be something too simple to see.
any help would be appreciated!
Sub PDFSet()
Dim c As Variant
Dim sName As String
Dim sh As Worksheet
Dim OrgSheet As String
Dim ExportFileName As String
Dim sheetnames() As String
Dim i As Integer
Dim DefaultFileName As String
i = 0
DefaultFileName = SortableDateString(Now())
OrgSheet = ActiveSheet.Name
ExportFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name &
"." & DefaultFileName & ".pdf")
If ExportFileName = "" Then
'If ExportFileName = False Then ' was causing error so changed to
zero
length string as test
End
End If
On Error GoTo er
For Each c In Selection
If Len(c.Value) > 0 Then
ReDim Preserve sheetnames(i)
sheetnames(i) = c.Value
i = i + 1
End If
Next c
Sheets(sheetnames).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,
Filename:=ExportFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Debug.Print "sheets to print " & UBound(sheetnames)
Sheets(OrgSheet).Select
er:
If err.Number = 9 Then
MsgBox "The Sheet named - " & sName & " - does not exist"
Resume Next
End If
End Sub