The macro I am currently writing in excel VBA has the problem of causing the entire excel workbook to crash on some occasions while having no crash effect on other occasions. In between running the macro and having it crash/not crash I will make tiny changes to the code which could "cause" the macro to crash, but when I restart excel and make the changes again, the macro runs smoothly without crashing. Would anybody be able to help me out and see if there is a loop or something that I am not closing or a coding error I am doing? I am not used to coding in VBA so I'm not completely sure of all the nuances.
Sub Printing_Preference()
'Printer Preferences Macro Coding
'==========
Worksheets("Sheet1").Activate
'NOTE:::::The below code does not set up every page to have the same column headers and row headers, instead it simply sets up one or the other and you must manually update the sheet information in order to have a column "copy" over vertically or a row "copy" over horizontally
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1" 'Sets the rows 1 to 1 to copy over to each page vertically, i.e. the first row on each sheet will be automatically mimicked to each sheet vertically below it and there forth
.PrintTitleColumns = "$A:$A" 'Sets the columns A to A to copy over to each page horizontally, i.e. the first column on each sheet will be automatically mimicked to each sheet horizontally next to it and there forth
End With
With Worksheets("Sheet1").PageSetup 'Set the page setup below this line, all page setup values can be found here https://msdn.microsoft.com/en-us/library/office/dn254188.aspx
.BlackAndWhite = False 'True sets the printer to print in black and white, false sets the printer to print in color
.LeftMargin = Application.InchesToPoints(0.5) 'left margin in inches, normal size is 0.7
.RightMargin = Application.InchesToPoints(0.5) ' right margin in inches, normal size is 0.7
.TopMargin = Application.InchesToPoints(0.5) 'top margin in inches, normal size is 0.75
.BottomMargin = Application.InchesToPoints(0.5) 'bottom margin in inches, normal size is 0.75
.HeaderMargin = Application.InchesToPoints(0.2) 'header margin in inches, normal size is 0.3
.FooterMargin = Application.InchesToPoints(0.2) 'footer margin in inches, normal size is 0.3
.PaperSize = xlPaper11x17 'Set paper size to tabloid 11x17, other options are found at https://msdn.microsoft.com/en-us/library/office/ff839964.aspx
.PrintArea = False 'Sets printing area to be the entire workbook, in order to set this as a specific area in the workbook, see the line below
'ActiveSheet.PageSetup.PrintArea = "$A$1:$G$11" 'Set printing area to be in the range specified within the quotations
.Orientation = xlLandscape 'Sets printing orientation to Landscape, use xlPortrait to set orientation to portrait
.PrintHeadings = False 'Sets the row/column headings to print if the value is true, sets them to not print if the heading is false, i.e. Row A, Row B, Row C..etc., Column A, Column B, Column C..etc.
End With
'Potential fix for the automatic double sided printing style of these printers, takes each page into account and prints each page 1 at a time rather than the entire document at once,
'if the "'" is removed from the code below, it will cause the entire worksheet to print out after running the macro, only remove the "'" when you are ready to print your document
'Dim i As Long
' For i = 1 To ActiveSheet.HPageBreaks.Count + 1
'ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, Copies:=1
'Next i
End Sub
Also at the end of my code where I have the single sided page printing code,(The part with Dim i as long, for i=1..........) "commented", out, is there a way to fix this so that it will only print when I want it to print instead of whenever I run the code WITHOUT, having to comment out that entire portion of code whenever I just want to run the code?
Bookmarks