Chris Macro
Well-known Member
- Joined
- Nov 2, 2011
- Messages
- 1,345
- Office Version
- 365
- Platform
- Windows
I have created a macro that formats my tabs so that they can be printable. After the macro is run and I check the page setup options, my tabs are being defaulted to "Adjust to" in the Scaling section as oppose to "Fit to". In the "Fit to" section it correctly displays the width = 1 and the height = 20, however it is not selected; so I have to manually select "Fit to". Is there a way to get around this manual step? (this part is occurring towards the end of the code) Thanks!
Code:
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Print_Format()<br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TabNameArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> HeaderNameArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>TabNameArray = Array("Assets", "Liabilities", "Income Stmt")<br>HeaderNameArray = Array("Assets", "Liabilities", "Income Statement")<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> x = 0 <SPAN style="color:#00007F">To</SPAN> 2<br><br><SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets(TabNameArray(x))<br> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row<br><br> <SPAN style="color:#007F00">'Add Filter</SPAN><br> .Range("A2") = "View Filter"<br> .Range("A2:A" & LastRow).AutoFilter<br> <br> <br> <SPAN style="color:#007F00">'Format Column "A"</SPAN><br> .Range("B2:B" & LastRow).Copy<br> .Range("A2").PasteSpecial Paste:=xlPasteFormats<br> Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br> <br> <SPAN style="color:#007F00">'Hide two December columns</SPAN><br> .Columns("D:E").Hidden = <SPAN style="color:#00007F">True</SPAN><br> <br> <SPAN style="color:#007F00">'How many columns are there</SPAN><br> <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 40<br> <SPAN style="color:#00007F">If</SPAN> IsEmpty(.Cells(2, y)) = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> Lastcolumn = y - 1<br> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> y<br> <br> <br> <SPAN style="color:#007F00">'Select Print Area (only excepts a range formatted in a string)</SPAN><br> .PageSetup.PrintArea = .Range("B1", .Cells(LastRow, Lastcolumn)).Address<br> .PageSetup.PrintTitleRows = "$1:$2"<br> .PageSetup.CenterHeader = HeaderNameArray(x)<br> .PageSetup.CenterFooter = "&Z&F"<br> .PageSetup.RightFooter = "&P"<br> .PageSetup.LeftMargin = Application.InchesToPoints(0.25)<br> .PageSetup.RightMargin = Application.InchesToPoints(0.25)<br> .PageSetup.TopMargin = Application.InchesToPoints(0.5)<br> .PageSetup.BottomMargin = Application.InchesToPoints(0.5)<br> .PageSetup.Orientation = xlLandscape<br> .PageSetup.FitToPagesWide = 1<br> .PageSetup.FitToPagesTall = 20<br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> x<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Code:
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Print_Format()<br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> TabNameArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> HeaderNameArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>TabNameArray = Array("Assets", "Liabilities", "Income Stmt")<br>HeaderNameArray = Array("Assets", "Liabilities", "Income Statement")<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> x = 0 <SPAN style="color:#00007F">To</SPAN> 2<br><br><SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets(TabNameArray(x))<br> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row<br><br> <SPAN style="color:#007F00">'Add Filter</SPAN><br> .Range("A2") = "View Filter"<br> .Range("A2:A" & LastRow).AutoFilter<br> <br> <br> <SPAN style="color:#007F00">'Format Column "A"</SPAN><br> .Range("B2:B" & LastRow).Copy<br> .Range("A2").PasteSpecial Paste:=xlPasteFormats<br> Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br> <br> <SPAN style="color:#007F00">'Hide two December columns</SPAN><br> .Columns("D:E").Hidden = <SPAN style="color:#00007F">True</SPAN><br> <br> <SPAN style="color:#007F00">'How many columns are there</SPAN><br> <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 40<br> <SPAN style="color:#00007F">If</SPAN> IsEmpty(.Cells(2, y)) = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> Lastcolumn = y - 1<br> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> y<br> <br> <br> <SPAN style="color:#007F00">'Select Print Area (only excepts a range formatted in a string)</SPAN><br> .PageSetup.PrintArea = .Range("B1", .Cells(LastRow, Lastcolumn)).Address<br> .PageSetup.PrintTitleRows = "$1:$2"<br> .PageSetup.CenterHeader = HeaderNameArray(x)<br> .PageSetup.CenterFooter = "&Z&F"<br> .PageSetup.RightFooter = "&P"<br> .PageSetup.LeftMargin = Application.InchesToPoints(0.25)<br> .PageSetup.RightMargin = Application.InchesToPoints(0.25)<br> .PageSetup.TopMargin = Application.InchesToPoints(0.5)<br> .PageSetup.BottomMargin = Application.InchesToPoints(0.5)<br> .PageSetup.Orientation = xlLandscape<br> .PageSetup.FitToPagesWide = 1<br> .PageSetup.FitToPagesTall = 20<br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> x<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>