macro break page for each range based on ending of month

[Alaa mg]

Hi Guys !

I search for macro to create break page for each range based on ending of month into first sheet . so the range is from column A: H . the column A is date DD\MM\YYYY .

so when end the date for each month for each range like 28 or 30 or 31 , then should break page after number of ending for that month for each range separately from the beginning to end and move to next range and create break page based on number of ending for that month so on .
thanks

 

[Joe4]

OK, the only thing I needed to update was the dates, as I am using the US version, so "10/1/2021" translates to October 1, 2021 for me, and not January 10, 2021.
If you are using a European version, and all the dates are indeed entered as dates and not text, the last code I posted should work for you, as the DAY function is not version specific.
(It worked for me after I fixed the dates in my date column so all my entries were valid dates).

Note that you may need to clear prior page breaks before running the code again, as if you ran previous code, you would still have those page breaks in there too.

 

[Alaa mg]

based on your code the data start from row 3 i change to 2 or 1 but it gives error application defined or bject defined error in this line

Rows(r - 1).PageBreak = xlPageBreakManual

 

[Joe4]

based on your code the data start from row 3 i change to 2 or 1 but it gives error application defined or bject defined error in this line

Rows(r - 1).PageBreak = xlPageBreakManual

No, don't mess with that part of the code!
If you start the loop at 1, then the calculation r-1 returns 0, which is not a valid row number and will result in errors!

Also, you always have a header in row 1, so you don't want to start on row 1.
And, if the first row of data (row 2) is the first of a month, then it would insert a page break.
Do you really want a page break BEFORE the first page of data? That would cause a blank sheet at the beginning of your printing.
So starting at row 3 seems appropriate.

 

[Alaa mg]

So starting at row 3 seems appropriate.

but when create break page in month JAN it ends after 28, but should be after 31

 

[Joe4]

but when create break page in month JAN it ends after 28, but should be after 31

It doesn't for me when I copied your data and tested it on my side.
Are you sure that isn't a holdover from some other attempt you made?
Did you clear ALL the page breaks before running the code?
Where does the second page end?
Are you printing landscape or portrait?

 

[Alaa mg]

Did you clear ALL the page breaks before running the code?

I create new file

Where does the second page end?

when preview printing it just shows three date for month JAN 29/01/2022,30/01/2022,31/01/2022 in the second page
it should show for ending of the first page.

Are you printing landscape or portrait?

portrait

 

[Joe4]

I create new file

when preview printing it just shows three date for month JAN 29/01/2022,30/01/2022,31/01/2022 in the second page
it should show for ending of the first page.

portrait

So that tells me that it is correctly placing a page break after January 31.
If there is one after January 28, I suspect either you are copying one over, or something else is doing that.

Try running this version of the code, and tell me what the MsgBox at the end returns:

Sub MyInsertPageBreaks()

    Dim lr As Long
    Dim r As Long
    Dim log As String
    
    Application.ScreenUpdating = False
    
'   Initialize log
    log = "Page breaks placed on rows:"
    
'   Find last cell with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting with row 3
    For r = 3 To lr
'       Check to see if column A is a date
        If IsDate(Cells(r, "A")) And Cells(r, "A") > 0 Then
'           Check to say if day of month in column A is 1
            If Day(Cells(r, "A")) = 1 Then
'               Insert pagebreak in row above day 1
                Rows(r - 1).PageBreak = xlPageBreakManual
'               Log results
                log = log & vbCrLf & r - 1
            End If
        End If
    Next r
        
    Application.ScreenUpdating = True
    
    MsgBox log
    
End Sub

 

[Alaa mg]

So that tells me that it is correctly placing a page break after January 31.

you're right . I misunderstood how the code works . I thought each month show in separately page

Try running this version of the code, and tell me what the MsgBox at the end returns:

33,62

 

[Joe4]

you're right . I misunderstood how the code works . I thought each month show in separately page

33,62

That proves that the VBA code is only inserting page breaks at rows 33 and 62.
If you are also getting one up in row 30, that is coming from some place else. It is not coming from the VBA code I provided.

If you do a Print Preview BEFORE your run the VBA code, where does it want to put the page breaks?
Where is the first break happening?
Are you sure it isn't just a "natural" page break that Excel is applying because you have run out of room on the sheet?

 

[Alaa mg]

If you do a Print Preview BEFORE your run the VBA code, where does it want to put the page breaks?

I no know what I ask for it can achieve by vba . my request is each speparately month should show in individual page.

for instance the page1 should show for month JAN from 1:31 and the page 2 should show for month FEB from 1: 28 and 1:30 in another page and so on for whole month