Snasui Excel Forum

Code: Select all

Sub ReportRetriev()
    Dim j As Integer
    Dim c, rng As Range
        On Error Resume Next
       Set rng = Sheets(2).Range("a6:a" & Sheets(2).Range("a" & Rows.Count).End(xlUp).Row)
            For Each c In rng
            With Sheets(4)
                j = .Range("e" & .Rows.Count).End(xlUp).Row + 1
                    If c.Value = .Cells(3, 9) Then
                        .Cells(j, 3).Value = c.Offset(, 2).Value
                        .Cells(j, 4).Value = c.Offset(, 3).Value
                        .Cells(j, 5).Value = c.Offset(, 5).Value
                        .Cells(j, 6).Value = c.Offset(, 6).Value
                        .Cells(j, 7).Value = c.Offset(, 1).Value
                        .Cells(j, 8).Value = c.Offset(, 7).Value
                        .Cells(j, 9).Value = c.Offset(, 9).Value
                    End If
                End With
            Next c
End Sub

Code: Select all

Sub SReportRetriev()
    Dim j As Integer
    Dim l As Long
    Dim c, rng As Range
        On Error Resume Next
       Set rng = Sheets(2).Range("a6:a" & Sheets(2).Range("a" & Rows.Count).End(xlUp).Row)
            For Each c In rng
            With Sheets(3)
                j = .Range("e" & .Rows.Count).End(xlUp).Row + 1
                    If c.Value = .Cells(3, 9) Then
                        .Cells(j, 3).Value = c.Offset(, 2).Value
                        .Cells(j, 4).Value = c.Offset(, 3).Value
                        .Cells(j, 5).Value = c.Offset(, 5).Value
                        .Cells(j, 6).Value = c.Offset(, 6).Value
                        .Cells(j, 7).Value = c.Offset(, 1).Value
                        .Cells(j, 8).Value = c.Offset(, 7).Value
                        .Cells(j, 9).Value = c.Offset(, 9).Value
                        If j = 35 Then
                       j = j + 6
                        .Cells(j, 3).Value = c.Offset(, 2).Value
                        .Cells(j, 4).Value = c.Offset(, 3).Value
                        .Cells(j, 5).Value = c.Offset(, 5).Value
                        .Cells(j, 6).Value = c.Offset(, 6).Value
                        .Cells(j, 7).Value = c.Offset(, 1).Value
                        .Cells(j, 8).Value = c.Offset(, 7).Value
                        .Cells(j, 9).Value = c.Offset(, 9).Value
      
                       End If
                    End If
                End With
            Next c
              
 '       With Sheets(3)

     '       l = .Range("C35" & .Rows.Count).End(xlUp).Row + 1
       
    '        .Range("C41" & l).Select
     '   End With

End Sub

Code: Select all

Dim j As Integer, k As Integer
Dim c As Range, rng As Range
On Error Resume Next
Set rng = Sheets(2).Range("a6:a" & Sheets(2) _
    .Range("a" & Rows.Count).End(xlUp).Row)
j = 5
For Each c In rng
    With Sheets(3)
        If k = 30 Then
            j = j + 12
            k = 0
        Else
            j = j + 1
        End If
        If c.Value = .Cells(3, 9) Then
            .Cells(j, 3).Value = c.Offset(, 2).Value
            .Cells(j, 4).Value = c.Offset(, 3).Value
            .Cells(j, 5).Value = c.Offset(, 5).Value
            .Cells(j, 6).Value = c.Offset(, 6).Value
            .Cells(j, 7).Value = c.Offset(, 1).Value
            .Cells(j, 8).Value = c.Offset(, 7).Value
            .Cells(j, 9).Value = c.Offset(, 9).Value
            k = k + 1
        End If
    End With
Next c

Code: Select all

j = j + 12

Code: Select all

j = j + 6