Since a lot of extra stuff crept into that main formatting section, then for tidiness, it might be best to change that section into a With End With Section
Code: Select all
For Cnt = 34 To ((Segs * 27) + ((Segs - 1) * 7) + 7) Step 34
With ThisWorkbook.Worksheets("consultant doctor")
' Most borders
.Range("A" & Cnt & "").Offset(-27, 0).Resize(29, 24).Borders.LineStyle = xlContinuous
' Sum formulas
.Range("D" & Cnt + 1 & ":X" & Cnt + 1 & "").Value = "=IF(SUM(R[-28]C:R[-1]C)=0,"""",SUM(R[-28]C:R[-1]C))"
.Range("D" & Cnt + 7 & ":X" & Cnt + 7 & "").Value = "=R[-6]C"
' First signature Second signature third signature Fourth signature Fifth signature
.Range("A" & Cnt + 2 & ":X" & Cnt + 2 & "").Value = Array("", "First signature", "", "", "", "", "Second signature", "", "", "", "", "Third signature", "", "", "", "", "Fourth signature", "", "", "", "", "Fifth Signature", "", "")
' Bold stuff
.Range("A" & Cnt & "").Offset(1, 0).Resize(7, 24).Font.Bold = True
.Range("A" & Cnt + 1 & "").Value = "The total"
.Range("A" & Cnt + 7 & "").Value = "Previous total"
' HPageBreaks.Add
.HPageBreaks.Add .Range("A" & Cnt + 7 & "")
End With ' ThisWorkbook.Worksheets("consultant doctor")
Next Cnt
_.__________________________-
Finally
Some notes, just for future reference to compare what is going on in this Thread and similar other recent thread
This might be useful for future reference to remind of what is going on for future similar requirements…
What is going on is almost identical, and these are the main differences between the final solution here and Hans solution
In Hans solution the data array is pasted out in one go, and then the Range.Sort is done on the pasted out data and then the extra lines are inserted and then the extra lines filled in.
Mine sorts the data in the data array before pasting out and also includes / “inserts” extra empty rows in that data array before pasting out, so that there is no need to insert the extra lines. (Those lines effectively come as extra empty data rows when the data array is pasted out)
The extra lines are filled in a similar way.
I don’t have enough experience yet to know the relative merits of the slightly different approach. I expect the Range.Sort might be something that works better than sorting within the array . I am not sure.
Not having to do the code line to insert lines is probably theoretically a time saver, but since both macros do a lot of filling in of those lines, then that adds so much extra time that that minor time improvement is not noticeable.
An improvement in my macro could be to replace some of the simpler row filling in of for example words, by adding in the data array before pasting out the data array. But once again, since a lot of spreadsheet interaction would still take place in the various formatting, then that improvement would not be noticeable
( As I mentioned previously, my macros can always be improved a bit with the Application.ScreenUpdating = False stuff. I just prefer to leave that out when developing and sharing macros, assuming the OP can easily add that bit themselves )
Alan