![]() ![]() Many people use it at work to create ad-hoc reports for different departments or projects. Ws.Columns(newyearcol).Excel is a handy tool for keeping track of all sorts of data. XlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _įor mycol = newyearcol To overallendcol - 1 Step 12 XlNext, MatchCase:=False, SearchFormat:=False).ActivateĬells.Find(What:="totals", After:=ActiveCell, LookIn:= _ XlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 'insert the required page breaks automaticallyĪ = xlPageBreakNoneĬells.Find(What:="Jan", After:=ActiveCell, LookIn:= _ This is completely brilliant - thank you!įor those who might be interested in creating VERTICAL page breaks - eg for each year in a cash flow - I share my code as below: Sub pagebreaks() ![]() Ws.Rows(iRow).PageBreak = xlPageBreakManual LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'find last used row in column A ![]() Set ws = ThisWorkbook.Worksheets("Sheet1") So something like this would be a possible result: Option Explicit LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row 'find last used row in column A Where LastRow is the last used row eg like Dim LastRow As Long Worksheets("Sheet1").Columns("J").PageBreak = xlPageBreakManualĪnd to get every 8ᵗʰ row after row 10 use a loop like Dim iRow As Long Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual Worksheets("Sheet1").VPageBreaks.Add Before:=Worksheets("Sheet1").Columns("J") You can add page breaks like this: Worksheets("Sheet1").HPageBreaks.Add Before:=Worksheets("Sheet1").Rows(25) I tried adding this loop to manually add the page break, but it bogged the code way down, so I was wondering if there is a simpler solution that I am not aware of. FooterMargin = Application.InchesToPoints(0.3) HeaderMargin = Application.InchesToPoints(0.3) BottomMargin = Application.InchesToPoints(0.75) TopMargin = Application.InchesToPoints(0.75) RightMargin = Application.InchesToPoints(0) LeftMargin = Application.InchesToPoints(0) CenterFooter = "&""Chiller""&75 PLACARD" Here is the code that I am currently using: ActiveSheet.ResetAllPageBreaksĪctiveSheet.VPageBreaks(2).DragOff Direction:=xlToRight, RegionIndex:=1ĪctiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 The problem that I am having is that when using the "ActiveWindow.View = xlPageBreakPreview" method, the first page break occurs after row 10, and then from there on out, occurs every 8 rows. I'm trying to insert page breaks that will allow every 8 rows of the excel sheet to be printed. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |