# VBA Code Update Save as PDF



## Jimmypop (Thursday at 6:04 AM)

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 (Thursday at 7:27 AM)

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 (Thursday at 8:20 AM)

bferraz said:


> 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 (Thursday at 8:22 AM)

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 (Thursday at 8:23 AM)

bferraz said:


> 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 (Thursday at 8:42 AM)

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 (Thursday at 9:12 AM)

Jimmypop said:


> 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 (Thursday at 9:16 AM)

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 (Thursday at 9:19 AM)

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 (Thursday at 9:36 AM)

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


----------



## Jimmypop (Thursday at 6:04 AM)

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


----------



## Jimmypop (Yesterday at 2:52 AM)

Ok...after some debugging I found that there was some code interference... Everytime Sheet1 activates there is code running to autofit some rows and columns... I added *Application. EnableEvents = False* to mitigate this....

Workbook is now exporting only the visible cells I want on Sheet1 and the whole of Sheet2 which in theory means I am almost there...

However... the copy made of Sheet1 does not copy shapes and images.... In my post #6 I know I said it is copying images... however after all my debugging I realised that it is not grabbing the copy sheet but was grabbing the original, hence the presence of the images...

Now i went and manually said make a copy of Sheet1 after Sheet1 and even this does not copy images and shapes🙈🙈


----------



## Jimmypop (Yesterday at 5:27 AM)

UPDATE... working now...


Ok so I found the issue that was preventing the copying of the images and shapes. The issue was in my settings. I needed to check "Cut, copy, and sort inserted objects with their parent cells" under "Cut, copy, and paste" in the Advanced options. Not sure why this was turned off seeing as to my knowledge it should be on by default. So, then I sat with an issue by not knowing if other users setting was the same. I overcame this by adding _*Application.CopyObjectsWithCells = True. *_I also did not set it to false again on purpose seeing as most of our excel documents and procedures in the company works by copying and pasting. So, this should prevent user from having to set this in settings every time before use.

There were also some places I needed to lock and unlock the sheet and prevent other code from running for this routine of exporting to pdf.

@bferraz thanks for the assistance given...👌😃🍻 updated code below



```
Sub Export_To_PDF()
    Application.DisplayAlerts = False
    Application.CopyObjectsWithCells = True
    Dim ws, ws1     As Worksheet
    Dim lastRow, lastCol As Long
    Dim WBName, FilePath As String
    Dim wsList      As Variant
    With ThisWorkbook
        Set ws1 = .Sheets("Sheet1")
        ws1.Copy After:=ws1
        Set ws = .Sheets(ws1.Index + 1)
    End With
    ActiveSheet.Unprotect "somepassword"
    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..."
    wsList = Array(ws.Name, "Sheet2")
    Application.EnableEvents = False
    ThisWorkbook.Sheets(wsList).Select
    ActiveSheet.ExportAsFixedFormat _
                                    Type:=xlTypePDF, _
                                    Filename:=FilePath, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True
    Worksheets("Sheet1").Select
    ws.Delete
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    ActiveSheet.Protect "somepassword"
End Sub
```


----------



## bferraz (Yesterday at 7:16 AM)

Jimmypop said:


> UPDATE... working now...
> 
> 
> Ok so I found the issue that was preventing the copying of the images and shapes. The issue was in my settings. I needed to check "Cut, copy, and sort inserted objects with their parent cells" under "Cut, copy, and paste" in the Advanced options. Not sure why this was turned off seeing as to my knowledge it should be on by default. So, then I sat with an issue by not knowing if other users setting was the same. I overcame this by adding _*Application.CopyObjectsWithCells = True. *_I also did not set it to false again on purpose seeing as most of our excel documents and procedures in the company works by copying and pasting. So, this should prevent user from having to set this in settings every time before use.
> ...



You are welcome, mate! 😀
I'm happy you figured that out. =)


----------

