Dear Readers,
 
In this article I am going to share with you – Two little VBA code which will help you in your day-to-day coding life.

1. How to disable Copying Objects with cells through VBA

When you are trying to move or copy a Sheet or set of Sheets in to another workbook then by default it copies everything. Even the objects lying in that cell, range or Sheet also gets copied in to new Sheet. You may not like to copy all the objects along with the cell contents all the time.

For example:

If you have a Summary page in Excel and you have put a “Download” button in that page which makes a copy of your Summary sheet in a new workbook. In this case in the downloaded Summary sheet you would definitely not like to keep the Download button.. right?? Because there is no point of keeping it there. Then here is the solution – before executing the copy statement you need to disable CopyObjectsWithCells method:


'Disable copying of objects with cells before executing the copy statement
Application.CopyObjectsWithCells = False
'Now Copy the Sheet in to a new Workbook
Sheets("Summary").Copy
'Now again enable the CopyObjectWithCells back
Application.CopyObjectsWithCells = True

2. VBA to Change the Orientation of the Sheet before printing

Sometimes you may need to change the orientation of the page before printing from Excel VBA. It could be used even while exporting your Sheet to PDF format. All you need to do is before executing the Print statement or Export statement you need to Set the Page orientation using below statement:


'To set the orientation as LandScape
Sheet1.PageSetup.Orientation = xlLandscape
'To set the orientation as Portrait
Sheet1.PageSetup.Orientation = xlPortrait

Example: Convert your Sheet in to pdf with LandScape Orientation


Sub Convert_To_PDF_LandScape()
    With ActiveSheet
    'First Set the orientation of the page
        .PageSetup.Orientation = xlLandscape
    'Now Export the Sheet to PDF
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Users\Vish\File_Name.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With
End Sub

Example: Convert your Sheet in to pdf with Portrait Orientation


Sub Convert_To_PDF_LandScape()
    With ActiveSheet
    'First Set the orientation of the page
        .PageSetup.Orientation = xlPortrait
    'Now Export the Sheet to PDF
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Users\Vish\File_Name.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With
End Sub

To read more about converting excel sheet in to pdf click here

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!