Loop within a loop

[owen4512]

Hi all,

I've created the below vba however it's not quite working how i would like it to. I'm looking to use value in "N11" to search rows of data in sheet2, copy the data if it matches "N11" and paste that on sheet3. The issue i'm having is when pasting on sheet3 the lastrow doesnt work so the data keeps pasting into row 2 and overriding the previous result.

Any help would be much appreciated

Sub Search()

    Dim i_D, i_A, last_row_Data, last_row_Active As Integer
    Dim Search, Records As Long
   
    Search = Sheet1.Range("N11").Value
 
    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
    
    For i_A = 2 To last_row_Active
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
           
            Records = Records + 1
                Sheet2.Activate
                Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
                
                Sheet3.Activate
                Sheet3.Range(Cells(i_A, 1), Cells(i_A, 14)).PasteSpecial xlPasteValuesAndNumberFormats

           
        Else
           
        End If
        
    
    Next i_D
    Next i_A
    
    Sheet1.Range("N13").Value = Records


End Sub

 

[mart37]

Why the second loop?

    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
            Records = Records + 1
            Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
            last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
            Sheet3.Range(Cells(last_row_Active, 1), Cells(last_row_Active, 14)).PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i_D

 

[owen4512]

Why the second loop?

    last_row_Data = Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) + 1 'Finds last row on 'All-Data' tab
    For i_D = 2 To last_row_Data
        If Sheet2.Range("B" & i_D).Value = Search Then
            Records = Records + 1
            Sheet2.Range(Cells(i_D, 1), Cells(i_D, 14)).Copy
            last_row_Active = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1 'Finds last row on 'Active Data' tab
            Sheet3.Range(Cells(last_row_Active, 1), Cells(last_row_Active, 14)).PasteSpecial xlPasteValuesAndNumberFormats
        End If
     Next i_D

Thank you for this - I have tried it and initially it didnt work but i added in Sheet2.Activate & Sheet3.Activate and this now works perfectly. Thanks again