Excel - Many sheets into one sheet

[MarjiS]

I am copy/pasting info from multiple sheets into one master sheet. The format is the same on all sheets. Here is the code I'm using (simplified):

Private Sub btnCreate_Click()
Dim i, P1i, P2i
i = 2
For P1i = 2 To 10
    If Sheet2.Cells(P1i, 1) <> "" Then
        Sheet2.Rows(P1i).Copy
        'Sheet1.Rows(i).PasteSpecial
        Sheet1.Rows(i).PasteSpecial Paste:=xlPasteValues
        i = i + 1
    Else
        Exit For
    End If
Next
For P2i = 2 To 10
    If Sheet3.Cells(P1i, 1) <> "" Then
        Sheet3.Rows(P1i).Copy
        'Sheet1.Rows(i).PasteSpecial
        Sheet1.Rows(i).PasteSpecial Paste:=xlPasteValues
        i = i + 1
    Else
        Exit For
    End If
Next
End Sub

Sheet1 and Sheet2 contain 5 rows of information followed by 5 blank rows.

The subroutine stops on Sheet1 after correctly pasting the rows from Sheet2, with a msg of 'Select destination and press ENTER or choose paste'. It does not continue to copy/paste from Sheet3. The last pasted row on Sheet1 is selected and its corresponding copied row on Sheet2 is selected with the flashing dashes.

I have used the following code variation successfully but I'm trying to avoid using .Select.

i = 2
For P1i = 2 To 10
    If Sheet2.Cells(P1i, 1) <> "" Then
        Sheet2.Rows(P1i).Copy
        Sheet1.Rows(i).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        i = i + 1
    Else
        Exit For
    End If
Next

What am I missing?

Thanks,
Marji

 

[shetlandbob]

i tried your exact code, and it copies it fine for me?

I cant see what the problem is?

Robert Cumming

http://www.consultantengineer.co.uk

 

[shetlandbob]

apart from on Sheet3 it copies the same row over and over...

    If Sheet3.Cells(P1i, 1) <> "" Then
        Sheet3.Rows(P1i).Copy
[green]' should be[/green]
    If Sheet3.Cells(P2i, 1) <> "" Then
        Sheet3.Rows(P2i).Copy

Robert Cumming

http://www.consultantengineer.co.uk

 

[MarjiS]

*looks embarrassed*
Well, duh.
I've gotten so used to getting syntax errors that I didn't think to look for error in my code.
Thanks, Bob.