The code below adds a new worksheet to a summary workbook for every pagebreak in 2 other workbooks. I want to copy data from the other workbooks to each added sheet in the summary workbook. All of the data keeps copying only to sheet1. Thank you in advance for any help.
For n = 1 To Workbooks(file1name).Worksheets("Sheet1").HPageBreaks.Count
i = i + 1 'integer that tracks the pages being added
'SourceRange is a the range of data being copied
Set SourceRange = Workbooks(file1name).Worksheets("Sheet1").Range("A" & pgBreak, "Q" & Workbooks(file1name).Worksheets("Sheet1").HPageBreaks(n).Location.Row - 1)
SourceRange.Copy
summary.Sheets.Add 'summary is the summary workbook I would like to copy the data to
summary.Sheets(i).Activate
ActiveSheet.Paste
For j = 1 To Workbooks(file2name).Worksheets("Sheet1").HPageBreaks.Count
If matchVar = matchVar2 Then 'If the variable in workbook 1 matches the variable in workbook 2 than copy the information in between the page breaks
i = i + 1
Set SourceRange = Workbooks(file2name).Worksheets("Sheet1").Range("A" & pgBreak2, "Q" & Workbooks(file2name).Worksheets("Sheet1").HPageBreaks(j).Location.Row - 1)
SourceRange.Copy
summary.Sheets.Add
summary.Sheets(i).Activate
ActiveSheet.Paste
End If
Next
Excel adds the new sheet by default before active sheet, so your "Sheet1" is shifted each time you add a new sheet as that was the active one.
If you see the documentation of worksheet.add method you see that the newly created sheet is also activated, so don't need to activate it again, just paste your data ;)