macro break page for each range based on ending of month

A

Alaa mg

Active Member
Joined
May 29, 2021
Messages
343
Office Version
  1. 2019
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Since the number of months in each month is different, it might be better to identify the beginning of the following month, and put the page break above that.
Does your listing include EVERY day of the month sorted in order, so that every months begins with day "1"?
 
Upvote 0
OK, see if this works for you:
VBA Code: 
Sub MyInsertPageBreaks()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   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 say if day of month in column A is 1
        If Day(Cells(r, "A")) = 1 Then
'           Insert pagebreak
            Rows(r).PageBreak = xlPageBreakManual
        End If
    Next r
       
    Application.ScreenUpdating = True
   
End Sub
 
Upvote 0
it's great ! but somtimes gives error mismatch in this line
VBA Code: 
If Day(Cells(r, "A")) = 1 Then
if I run the macro repeatedly .
how avoid it please?
also I would shows the headers for each range .
 
Upvote 0
Sounds like you have some non-date entries in column A (headers, etc), that are messing with the formula.
Can you post a sample of the data, that shows the month switch-over, so I can see how many rows you have in headers/trailers?
 
Upvote 0
If your header is only one row, then this should work:
VBA Code: 
Sub MyInsertPageBreaks()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   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
            End If
        End If
    Next r
        
    Application.ScreenUpdating = True
    
End Sub
If it does not work, then I will really need to see an example of what your data looks like.
 
Upvote 0
If your header is only one row, then this should work:
Click to expand...
it doesn't work at all .
for each month contain headers.
headers begins directly after number of ending the month . no empty rows among the months .

all of data are consecutive without any empty row among them
 
Upvote 0
OK, I will ask for a 3rd time. Please post a sample of your data.
Without seeing your data, I am only guessing as to what it looks like, and apparently I am not guessing right if it does not work.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
OK, I will ask for a 3rd time. Please post a sample of your data.
Click to expand...
sorry ! I thought you don't need it after I explained for your even that the tool doesn't show huge data.
co.xlsm
ABCDEFGH
1DATECODEBRANDTYPECUSTOMER NUMBERINVOICE NUMBERORIGINQTY
201/01/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL110
302/01/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL110
403/01/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL240
504/01/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL210
605/01/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL35
706/01/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL45
807/01/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL55
908/01/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL210
1009/01/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL120
1110/01/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL130
1211/01/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL240
1312/01/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL250
1413/01/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL360
1514/01/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL470
1615/01/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL580
1716/01/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL290
1817/01/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1100
1918/01/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1110
2019/01/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2120
2120/01/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2130
2221/01/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3140
2322/01/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4150
2423/01/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL5160
2524/01/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL2170
2625/01/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1180
2726/01/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1190
2827/01/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2200
2928/01/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2210
3029/01/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3220
3130/01/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4230
3231/01/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL5240
33DATECODEBRANDTYPECUSTOMER NUMBERINVOICE NUMBERORIGINQTY
3401/02/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL2250
3502/02/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1260
3603/02/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1270
3704/02/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2280
3805/02/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2290
3906/02/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3300
4007/02/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4310
4108/02/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL5320
4209/02/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL2330
4310/02/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1340
4411/02/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1350
4512/02/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2360
4613/02/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2370
4714/02/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3380
4815/02/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4390
4916/02/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL5400
5017/02/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL2410
5118/02/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1420
5219/02/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1430
5320/02/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2440
5421/02/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2450
5522/02/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3460
5623/02/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4470
5724/02/2021AA-104MM-FRSD-5CUS-100INV1-BSJ4-1005CCL5480
5825/02/2021AA-101VLG-FRESD-2CUS-104INV1-BSJ4-1006CCL2490
5926/02/2021AA-100ASGL-VENSD-1CUS-100INV1-BSJ4-1000CCL1500
6027/02/2021AA-100ASGL-VENSD-1CUS-101INV1-BSJ4-1001CCL1510
6128/02/2021AA-101VLG-FRESD-2CUS-102INV1-BSJ4-1002CCL2520
62DATECODEBRANDTYPECUSTOMER NUMBERINVOICE NUMBERORIGINQTY
6301/03/2021AA-101VLG-FRESD-2CUS-103INV1-BSJ4-1003CCL2530
6402/03/2021AA-102VMC-EZERSD-3CUS-100INV1-BSJ4-1000CCL3540
6503/03/2021AA-103C-ZERSD-4CUS-100INV1-BSJ4-1004CCL4550
1
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Page Breaks and Subscript out of Range
Replies
1
Views
453
Farscape2020
F
A
  • Question
Separate sheet into multiple sheets by dynamic range
Replies
0
Views
168
alonso85
A
D
  • Question
VBA Control Of Page Breaks
Replies
2
Views
927
DannyDont
D
F
  • Question
Checking for Page Breaks Multiple Times
Replies
2
Views
416
Shloime
S
S
  • Question
Mail Merge Macro Doesn't Connect Refresh
Replies
1
Views
69
StephenBart
S

Forum statistics

Threads
1,215,103
Messages
6,123,110
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top