need page break by asking the input file for page break to be done

I need some more help from you.

The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.

For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".

I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :

Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))

   Option Explicit
   Dim V_fileloc As String
   Dim oExcel As Excel.Application
   Dim oWB As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    Set oExcel = CreateObject("excel.Application")
    Set oWB = oExcel.Workbooks.Open(V_fileloc)
    oExcel.Worksheets("Sheet1").Activate
    oExcel.Sheets("Sheet1").Select
 
    Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
    On Error Resume Next
    oExcel.ResetAllPageBreaks
    oExcel.PageSetup.PrintArea = ""
    For Each cl In rng
        If cl.Value <> cl.Offset(1, 0).Value Then
            oExcel.HPageBreaks.Add Before:=cl.Offset(1, 0)
        End If
    Next cl
    
    oWB.Close
    Set oWB = Nothing
           
    oExcel.Quit
    Set oExcel = Nothing

    On Error GoTo 0
End Sub

Why are you creating a new instance of Excel?

Option Explicit

Dim V_fileloc  As String
Dim oExcel     As Excel.Application
Dim oWB        As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    '    Set oExcel = CreateObject("excel.Application")
    Set oWB = Workbooks.Open(V_fileloc)
    With oWB.Worksheets("Sheet1")

        Set rng = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
        On Error Resume Next
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        For Each cl In rng
            If cl.Value <> cl.Offset(1, 0).Value Then
                oExcel.HPageBreaks.Add Before:=cl.Offset(1, 0)
            End If
        Next cl
    End With
'    oWB.Close
    Set oWB = Nothing
    On Error GoTo 0
End Sub

Try the code that I posted

Originally Posted by royUK

Try the code that I posted

where is the code ? i think you don't understand th problem.

Your code for page break work fine. what i required is that ? I receive the file which want to be page break with out adding the macro to it.

so that what i plan to do is that, i will paste your existing page break code which you provided before in the file name "master". Current code will do a page break for "master" file, instead it need to ask the input file "transaction" file, the "transaction" file which need to page break. so that i can send the transaction file to client with page break on client id with out macro code in it.

2 Files need to be used, first one is "master" file which will contain your macro with reqired code change. Second, file is that "transaction" file from client as source file which really need to have a page break depending on client ids.

I feel you might have understand the problem.

Thanks.

I edited your code & posted it in Post #2, you have even quoted it!

Try this

Option Explicit

Dim V_fileloc  As String
Dim oExcel     As Excel.Application
Dim oWB        As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    '    Set oExcel = CreateObject("excel.Application")
    Set oWB = Workbooks.Open(V_fileloc)
    With oWB.Worksheets("Sheet1")

        Set rng = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
        '        On Error Resume Next
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        For Each cl In rng
            If cl.Value <> cl.Offset(1, 0).Value Then
                .HPageBreaks.Add Before:=cl.Offset(1, 0)
            End If
        Next cl
    End With
    '    oWB.Close
    Set oWB = Nothing
    On Error GoTo 0
End Sub

Originally Posted by saikumar

Welcome.

But heading is not printing in all the pages.

Thanks.

What do you mean? You can't detect whether a cell is a heading or not. You should standardise your workbooks so they either all have headings or don't.

It would also save time if you stated your requirements fully in the first question

Option Explicit

Dim V_fileloc  As String
Dim oExcel     As Excel.Application
Dim oWB        As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    '    Set oExcel = CreateObject("excel.Application")
    Set oWB = Workbooks.Open(V_fileloc)
    With oWB.Worksheets("Sheet1")
        Select Case MsgBox("Does the sheet have a header row?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Header row")

            Case vbYes
                Set rng = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
            Case vbNo
                Set rng = .Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
        End Select

        On Error Resume Next
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        For Each cl In rng
            If cl.Value <> cl.Offset(1, 0).Value Then
                .HPageBreaks.Add Before:=cl.Offset(1, 0)
            End If
        Next cl
    End With
    '    oWB.Close
    Set oWB = Nothing
    On Error GoTo 0
End Sub

The code does not affect that function, you need to set rows to print on each page. This addition will print Row1 on each sheet

Option Explicit

Dim V_fileloc  As String
Dim oExcel     As Excel.Application
Dim oWB        As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    '    Set oExcel = CreateObject("excel.Application")
    Set oWB = Workbooks.Open(V_fileloc)
    With oWB.Worksheets("Sheet1")

        On Error Resume Next
          With .PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        For Each cl In rng
            If cl.Value <> cl.Offset(1, 0).Value Then
                .HPageBreaks.Add Before:=cl.Offset(1, 0)
            End If
        Next cl
    End With
    '    oWB.Close
    Set oWB = Nothing
    On Error GoTo 0
End Sub