Printable View
I have a Worksheet that I import a CSV file into and then use vba to format. the first column has a date and time format which can consist of multiple days (ie. 3/11/2025 13:21 and 3/12/2025 17:56) I need to have each day have its own page and each page can only have 25 rows. a page can have less rows but no more than 25. I can write up a code to page break at 25 but not sure how i can page break by date and 25 rows per page. any help would be greatly appreciated.
Does this work for you?
Code:Sub InsertPageBreaksByDateAndRowCount()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim rowCount As Long
Dim currentDate As Date
' Set the worksheet you are working with
Set ws = ThisWorkbook.Sheets("YourSheetName") ' Change "YourSheetName" to the actual sheet name
' Find the last row in Column A with data
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
' Initialize variables rowCount = 0
currentDate = ws.Cells(1, "A").Value
' Assume the first date is in A1
' Loop through the rows
For i = 1 To lastRow
' Check for date change
If ws.Cells(i, "A").Value <> currentDate Then
' Insert a page break before the current row if it's not the first row
If i > 1 Then
ws.Rows(i).PageBreak = xlPageBreakManual
End If
currentDate = ws.Cells(i, "A").Value
rowCount = 1
' Reset row count for the new date
Else
rowCount = rowCount + 1
End If
' Check if the row count for the current page exceeds 25
If rowCount > 25 Then
' Insert a page break before the current row
ws.Rows(i).PageBreak = xlPageBreakManual
rowCount = 1
' Reset row count for the new page
End If
Next i
MsgBox "Page breaks inserted based on date changes and a maximum of 25 rows per page.", vbInformation
End Sub
In your screen shot the Amount column appears to have a Daily Total.
Is that in the CSV?
This worked Perfect, Thank you Very much
Thank you for asking in this forum.
You mentioned you can break at 25 rows, but do you reset the count when a new date starts?
Welcome to VBAX henaleona. If your post was directed to me then look at line 24 in the module.