title: Merge Files by Pagebreak
body: <p>I want to write a program that merges two files by page break. For example if I had two files A and B, which each had 3 page breaks I want to create a new file by copying all data in file A before page break 1, than all data in file B before page break 1, than all data in file A between page break 1 and page break 2, than all data in file B between page break 1 and page break 2, etc. </p>
  
  <p>I have the following code which simply opens the two files and than copies the data from file A and than the data from file B. I cannot figure out how to change the code to merge the two loops so that the new file will copy all data in file A before page break 1, than all data in file B before page break 1, etc.</p>
  
  <p>Any help would be really appreciated! Thank you! </p>
  
  <pre><code>Sub Merge_Mchpg()
  'Open two workbooks
  Workbooks.Open (Workbooks("Filepath.xlsx")
  Workbooks.Open (Workbooks("Filepath.xlsx"))
  
  
  Dim pgBreak As Variant 
  Dim pgBreak2 As Variant 
  
  Dim pgbrk1 As Integer 'Define variable for first worksheet pagebreaks
  pgbrk1 = 1
  Dim pgbrk2 As Integer 'Define variable for second worksheet pagebreaks
  Dim SourceRange As Range 'Define the source range in the newworkbook
  pgbrk2 = 1
  Dim pgbrkAll As Integer 'Integer to keep track of location in new wkbk
  pgbrkAll = 1
  Workbooks.Add 'Create new summary workbook
  Dim rowDiff As Integer 'Integer to keep track of location in new wkbk
  For Each pgBreak In Workbooks("test1.xlsx").Worksheets("Sheet1").HPageBreaks
  
      Set SourceRange = Workbooks("test1.xlsx").Worksheets("Sheet1").Range("A" &amp; pgbrk1, "K" &amp; pgBreak.Location.Row - 1)
      SourceRange.Copy
      ActiveSheet.Range("A" &amp; pgbrkAll).PasteSpecial
      rowDiff = pgBreak.Location.Row - pgbrk1
  
     pgbrk1 = pgBreak.Location.Row
     pgbrkAll = pgbrkAll + rowDiff + 1
    Next
  
  For Each pgBreak2 In` Workbooks("test2.xlsx").Worksheets("Sheet1").HPageBreaks
              Set SourceRange = Workbooks("test2.xlsx").Worksheets("Sheet1").Range("A" &amp; pgbrk2, "K" &amp; pgBreak2.Location.Row - 1)
             SourceRange.Copy
              ActiveSheet.Range("A" &amp; pgbrkAll).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
              SkipBlanks:=False, Transpose:=False
               rowDiff = pgBreak2.Location.Row - pgbrk2
  
               pgbrk2 = pgBreak2.Location.Row
  
              pgbrkAll = pgbrkAll + rowDiff + 1
  
     Next
  
  
  End Sub
  </code></pre>