VBA Code Update Save as PDF

[Jimmypop]

Good day all

Some assistance required...

I have the following code...

Sub Export_To_PDF()
    Dim WBName, FilePath As String
    WBName = ActiveWorkbook.Name
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & WBName & ".pdf"
    MsgBox "This report will now be published To your Desktop As a .pdf File", vbInformation, "Message from Admin..."
    ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat _
                                    Type:=xlTypePDF, _
                                    Filename:=FilePath, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True
    Worksheets("Sheet1").Select
End Sub

Now... How can I update the code to have it select only the visible cells on Sheet1 and the entire Sheet2 and then export to pdf... Currently it exports the entire Sheet1 but would like to have it export only the visible cells and not the hidden ones on Sheet1... So in essence:

1. Select visible cells Sheet1
2. Select entire Sheet2
3. Export the selection to a pdf...

Thanks in advance

 

[bferraz]

The way I see you can go for two approaches:

1. Duplicate the Sheet and remove all hidden rows and columns. Using this new sheet and then deleting it later.

Sub Export_To_PDF()

    Dim ws, ws1 As Worksheet
    Dim lastRow, lastCol As Long
    Dim WBName, FilePath As String
    
    With ThisWorkbook
    
        Set ws1 = .Sheets("Sheet1")
        
        ws1.Copy After:=ws1
        
        Set ws = .Sheets(ws1.Index + 1)
    
    End With
    
    lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
    lastCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
    
    For i = lastRow To 1 Step -1
    If ws.Rows(i).Hidden = True Then ws.Rows(i).EntireRow.Delete
    Next
    
    For i = lastCol To 1 Step -1
    If ws.Columns(i).Hidden = True Then ws.Columns(i).EntireColumn.Delete
    Next
    
    WBName = ActiveWorkbook.Name
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & WBName & ".pdf"
    MsgBox "This report will now be published To your Desktop As a .pdf File", vbInformation, "Message from Admin..."
    ThisWorkbook.Sheets(Array(ws.Name, "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat _
                                    Type:=xlTypePDF, _
                                    Filename:=FilePath, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True
    Worksheets("Sheet1").Select
    
    Application.DisplayAlerts = False
    
    ws.Delete
    
    Application.DisplayAlerts = True
    
End Sub

2. Creating a new sheet, copying only visible cells (note that you might adjust the range because Excel may crash after copying all cells from the worksheet) and deleting it later.

Sub Export_To_PDF()

    Dim ws, ws1 As Worksheet
    Dim WBName, FilePath As String
    
    With ThisWorkbook
    
    Set ws = .Sheets("Sheet1")
    Set ws2 = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    
    End With
    
    ws.Range("A1:AZ10000").SpecialCells(xlCellTypeVisible).Copy 'adjust range
    ws2.[a1].PasteSpecial xlPasteValues
    ws2.[a1].PasteSpecial xlPasteFormats
    
    WBName = ActiveWorkbook.Name
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & WBName & ".pdf"
    MsgBox "This report will now be published To your Desktop As a .pdf File", vbInformation, "Message from Admin..."
    ThisWorkbook.Sheets(Array(ws2.Name, "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat _
                                    Type:=xlTypePDF, _
                                    Filename:=FilePath, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True
    Worksheets("Sheet1").Select

    Application.DisplayAlerts = False
    
    ws2.Delete
    
    Application.DisplayAlerts = True
End Sub

Those are the options I see, let me know if any of these works for you or if anyone has another idea on how to achieve this! =)

 

[Jimmypop]

2. Creating a new sheet, copying only visible cells (note that you might adjust the range because Excel may crash after copying all cells from the worksheet) and deleting it later.

This one seems to work so far...after minor adjustments to make everything fir to one page... the only issue now is there are shapes and images that also need to be copied that are not done...

 

[bferraz]

In my perspective, if you have shapes and images you should take the first option, since it actually duplicates your first sheet.
Did that option not work for you?

 

[Jimmypop]

In my perspective, if you have shapes and images you should take the first option, since it actually duplicates your first sheet.
Did that option not work for you?

Let me test it quickly

 

[Jimmypop]

Working better... however it does not seem to be deleting the hidden rows (these rows do contain some info but can be deleted). This throws my sheet back to over 8 pages because it is including the original hidden rows. Images are now copied as well but shapes are not... also not selecting and exporting sheet2

 

[Jimmypop]

Working better... however it does not seem to be deleting the hidden rows (these rows do contain some info but can be deleted). This throws my sheet back to over 8 pages because it is including the original hidden rows. Images are now copied as well but shapes are not... also not selecting and exporting sheet2

I have stepped through the code... On the newly created sheet it does in fact delete the hidden rows and when this sheet is selected together with sheet2 manually then exports' fine...

 

[bferraz]

I was about to recommend you to comment the ws.Delete row to check on the new Sheet but I guess you have figured that out.

Can you explain a little bit more about selecting the sheet? The Sheet2 and the new sheet are not being saved as pdf when the code runs?

 

[Jimmypop]

Only Sheet1 is saving as pdf...however in the pdf version it still looks as if the hidden rows are not deleted (8pages instead of 1) and then Sheet2 is not saved in the final pdf

 

[bferraz]

Probably it is still trying to print "Sheet1". Can you debug in code to check if ws.Name is returning "Sheet1" or the duplicate version of the sheet? Also can you double check if the name of the second sheet is "Sheet2" because I haven't made a change on the following structure "ThisWorkbook.Sheets(Array(ws2.Name, "Sheet2")).Select . It should still get the second sheet.

You could try to assign this to a variable to check.

Dim wsList As Variant
wsList = Array(ws.Name, "Sheet2")
ThisWorkbook.Sheets(wsList).Select

While you are debugging did you had the opportunity to see if the macro is selecting the sheets or did you really had to manually click on them? If so, the problem should be on this Select line