Dear Readers,
\n
\nIn this article I am going to share with you – Two little VBA code which will help you in your day-to-day coding life.<\/p>\n
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. <\/p>\n
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<\/strong> method:<\/p>\n 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:<\/p>\n 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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1679,1676],"tags":[],"class_list":["post-4224","post","type-post","status-publish","format-standard","hentry","category-excel-macro-beginner","category-excel-tips"],"yoast_head":"\n\r\n'Disable copying of objects with cells before executing the copy statement\r\nApplication.CopyObjectsWithCells = False\r\n'Now Copy the Sheet in to a new Workbook\r\nSheets(\"Summary\").Copy\r\n'Now again enable the CopyObjectWithCells back\r\nApplication.CopyObjectsWithCells = True\r\n<\/code><\/pre>\n2. VBA to Change the Orientation of the Sheet before printing<\/h1>\n
\r\n'To set the orientation as LandScape\r\nSheet1.PageSetup.Orientation = xlLandscape\r\n'To set the orientation as Portrait\r\nSheet1.PageSetup.Orientation = xlPortrait\r\n<\/code><\/pre>\nExample: Convert your Sheet in to pdf with LandScape Orientation<\/h2>\n
\r\nSub Convert_To_PDF_LandScape()\r\n With ActiveSheet\r\n 'First Set the orientation of the page\r\n .PageSetup.Orientation = xlLandscape\r\n 'Now Export the Sheet to PDF\r\n .ExportAsFixedFormat _\r\n Type:=xlTypePDF, _\r\n Filename:=\"C:\\Users\\Vish\\File_Name.pdf\", _\r\n Quality:=xlQualityStandard, _\r\n IncludeDocProperties:=True, _\r\n IgnorePrintAreas:=False, _\r\n OpenAfterPublish:=False\r\n End With\r\nEnd Sub\r\n<\/code><\/pre>\nExample: Convert your Sheet in to pdf with Portrait Orientation<\/h2>\n
\r\nSub Convert_To_PDF_LandScape()\r\n With ActiveSheet\r\n 'First Set the orientation of the page\r\n .PageSetup.Orientation = xlPortrait\r\n 'Now Export the Sheet to PDF\r\n .ExportAsFixedFormat _\r\n Type:=xlTypePDF, _\r\n Filename:=\"C:\\Users\\Vish\\File_Name.pdf\", _\r\n Quality:=xlQualityStandard, _\r\n IncludeDocProperties:=True, _\r\n IgnorePrintAreas:=False, _\r\n OpenAfterPublish:=False\r\n End With\r\nEnd Sub\r\n<\/code><\/pre>\n To read more about converting excel sheet in to pdf click here<\/a><\/h2>\n<\/span>","protected":false},"excerpt":{"rendered":"