Copying data to a added worksheet

55 views Asked by At

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
1

There are 1 answers

1
Máté Juhász On BEST ANSWER

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 ;)