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 using 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. How to Change the Orientation of the Sheet before printing or exporting to pdf using VBA

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