Excel VBA – Few little help in your day-to-day Coding
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.
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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box