page break or page set up in macro

Hey Somusas,

Sorry for the delay.

I have included the page break and printarea code in the original code itself -

Option Explicit
Dim Scount As Integer
Dim StRow As Long
Dim i As Long
Dim lrow As Long
Dim RowCount As Long
Dim prodname As String
Dim templrow As Long
Dim j As Long

Sub sep_producer()

StRow = 1
lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lrow + 1
    If Worksheets("Sheet1").Range("A" & i).Value Like "Report Period*" And i <> 1 Or i = lrow + 1 Then
        prodname = Right(Worksheets("Sheet1").Range("A" & StRow + 4).Value, Len(Worksheets("Sheet1").Range("A" & StRow + 4).Value) - 10)
        If Not Evaluate("ISREF('" & prodname & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = prodname
        End If
        templrow = Worksheets(prodname).Range("A" & Rows.Count).End(xlUp).Row
        Worksheets("Sheet1").Range("A" & StRow & ":H" & RowCount).Copy
        If Worksheets(prodname).Range("A1") = "" Then
            Worksheets(prodname).Range("A1").PasteSpecial Paste:=xlValues
            Worksheets(prodname).Range("A1").PasteSpecial Paste:=xlFormats
        Else
            Worksheets(prodname).Range("A" & templrow + 2).PasteSpecial Paste:=xlValues
            Worksheets(prodname).Range("A" & templrow + 2).PasteSpecial Paste:=xlFormats
        End If
        Worksheets(prodname).Cells.EntireColumn.AutoFit
        Worksheets(prodname).Columns("H:H").ColumnWidth = 37.14
        Worksheets(prodname).Cells.EntireRow.AutoFit
        StRow = i
        RowCount = RowCount + 1
        Worksheets(prodname).HPageBreaks.Add before:=Range("A" & i)
    ElseIf Worksheets("Sheet1").Range("A" & i).Value Like "Report Period*" And i = 1 Then
        RowCount = RowCount + 1
    Else
        RowCount = RowCount + 1
    End If
    
Next i

For j = 1 To Worksheets.Count
    With Worksheets(j)
        .PageSetup.PrintArea = ""
    End With
Next j

End Sub

Try adding this line -

Worksheets(prodname).VPageBreaks.Add after:=Range("G" & i)

after this line

Worksheets(prodname).HPageBreaks.Add before:=Range("A" & i)

What errors are you getting?