Chirag R Raval
Member
Dear All Experts,
Again Here , but now For Horizontal Page breaks.
Requirement to print 2 data sets on 1 single page.
Horizontal Page break require on every 2nd occurrence of particular text on cell.
Ignore Hidden Rows.
For example, on some sites,I already Try below,
https://answers.microsoft.com/en-us...l-macros/9976e30a-8aae-4bec-84e8-43b35b113ec2
https://answers.microsoft.com/en-us...or-excel/85790a6a-ef93-4354-8ad5-3cc5e4399285
https://www.extendoffice.com/documents/excel/1774-excel-insert-page-break-every-row.html
I already use Below code to put Hpagebreak on every found but now requirement raise as every 2nd occurrence of "DISPATCH JAN TO NOV-22".
This Loop check every cell that take more time, but I believe if use Range.Find method, then it can be more robust.
I am not knowing very well all aspects of VBA, but I daily use VBA in my many types of daily routine work & without it, I can't complete my work on time.
Currently I manually select 2 sets of data, adjust rows height to fit on A4, select -set-click print area & then print, and after print that I select below further 2 sets & do same thing, till sheet's data end, painfully pass whole my day, just for print 2 data set on 1 A4 page.
There are need to beware for Hidden rows which hides for reason (not requirement in print).
So, condition is only visible rows should be count for 2nd occurrence. I attached Screen shot of whole scenario for reference.
[Please Refer This Image as my Requirement & Situation]
Hundreds of data sets on this worksheet. There should be 2 sets of data as pair require on every A4 size page.
so obviously page break requires on every 2nd occurrence of particular text.
If, that happen successfully, I am ready to manually adjust rows height to readable fit 2 sets in A4 page, so I get whole sheet ready for print in one go.
Hope, I try my best to describe my situation if require further, please mention.
Can anyone help regarding this?
I really appreciate & will be thankful forever.
Regards, Chirag Raval
Again Here , but now For Horizontal Page breaks.
Requirement to print 2 data sets on 1 single page.
Horizontal Page break require on every 2nd occurrence of particular text on cell.
Ignore Hidden Rows.
For example, on some sites,I already Try below,
https://answers.microsoft.com/en-us...l-macros/9976e30a-8aae-4bec-84e8-43b35b113ec2
https://answers.microsoft.com/en-us...or-excel/85790a6a-ef93-4354-8ad5-3cc5e4399285
https://www.extendoffice.com/documents/excel/1774-excel-insert-page-break-every-row.html
I already use Below code to put Hpagebreak on every found but now requirement raise as every 2nd occurrence of "DISPATCH JAN TO NOV-22".
Code:
Sub Insert_Pagebreak_On_EveryFoundok()
Dim MYCOLUMN As Range
Dim MyCell As Range
'For Each MyCell In Range("G2:G" & Rows.Count).End.xlUp))
'For Each MyCell In Range Cells(Rows.Count, 2).End(xlUp).row 2
ActiveSheet.Range("G" & Rows.Count).End(xlUp).row))
ActiveWindow.view = xlPageBreakPreview
Set MYCOLUMN = ActiveSheet.Range("F2:F" & ActiveSheet.Range("F" & Rows.Count).End(xlUp).row)
For Each MyCell In MYCOLUMN
MyCell.Select
If MyCell.Value Like "*DISPATCH JAN TO NOV-22*" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add
ActiveCell.offset(1, 0)
Else
ActiveCell.offset(1, 0).Select
End If
Next
ActiveWindow.view = xlNormalView
End Sub
This Loop check every cell that take more time, but I believe if use Range.Find method, then it can be more robust.
I am not knowing very well all aspects of VBA, but I daily use VBA in my many types of daily routine work & without it, I can't complete my work on time.
Currently I manually select 2 sets of data, adjust rows height to fit on A4, select -set-click print area & then print, and after print that I select below further 2 sets & do same thing, till sheet's data end, painfully pass whole my day, just for print 2 data set on 1 A4 page.
There are need to beware for Hidden rows which hides for reason (not requirement in print).
So, condition is only visible rows should be count for 2nd occurrence. I attached Screen shot of whole scenario for reference.
[Please Refer This Image as my Requirement & Situation]
Hundreds of data sets on this worksheet. There should be 2 sets of data as pair require on every A4 size page.
so obviously page break requires on every 2nd occurrence of particular text.
If, that happen successfully, I am ready to manually adjust rows height to readable fit 2 sets in A4 page, so I get whole sheet ready for print in one go.
Hope, I try my best to describe my situation if require further, please mention.
Can anyone help regarding this?
I really appreciate & will be thankful forever.
Regards, Chirag Raval