Looping in Macro for 3 values to place in another worksheet

[DThib]

Hello,

Latest quandary. I need to set 3 conditions for a row of information to be chosen to have 8 columns copied and placed in another worksheet.
This should pull only those records that match these three conditions from a worksheet with ~30,000 rows.

Can anyone help me figure out how to set up 3 constraints before copying appropriate cells?
I haven't placed the cell result pull yet.

Private Sub WorkaSet()

Dim Rws As Long
Dim Rng As Range
Dim ws, sh As Worksheet
Dim c, a, b As Range
Dim x As Integer


    Set ws = Sheets("Workable")  'specify sheet name here to paste to
    x = 3   'begins pasting in Sheet RFQ on row 2
Application.ScreenUpdating = False


    Set sh = Sheets("Initial Query Pull")
    Set a = sh.Range("$K:$K")
    Set b = sh.Range("$Y:$Y")
            With sh
                For Each c In .Range("$AF:$AF")
                    If (c.Value <> "") And (a.Value = "Assigned") And (b.Value = "") Then
                       'If b.Value = "" Then
                      'searches for "FI ready" cells And (C1.Value = "Assigned")
                        c.EntireRow.Copy
                        ws.Range("B" & x).PasteSpecial Paste:=xlValues
                         
                        x = x + 1


                    End If
                Next c
            End With
    
ws.Range("A1").Select
Application.ScreenUpdating = True


End Sub

DThib

 

[DThib]

Thanks.
Yes, Row 1 is the header row.
A macro before the one I am trying to get working opens the Access query. The next macro copies the refreshed data to the "Initial Query Pull" sheet. The code I am working on is started at a command button that will fire this code to pull the data out of the query copy("Initial Query Pull") and place it in a sheet ("Workable") to be manipulated by other tasks.
The autofilter does not work since the macro should only place data that is meeting the three criteria(1st has text present, 2nd shows the text "Assigned" as its option, and 3rd the finish date is empty).
I have worked out the following, but it is not firing every time and I cannot figure out why

Sub Workie()


Dim LastRow As Long
Dim i As Long
Dim j As Long


LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row


'Sheets("Initial Query Pull").Select


j = 1
    For i = 1 To LastRow
    
        If Sheets("Initial Query Pull").Cells(i, 31) <> "" And Sheets("Initial Query Pull").Cells(i, 11) = "Assigned" And Sheets("Initial Query Pull").Cells(i, 25) = "" Then
             Sheets("Workable").Cells(j, 2) = Sheets("Initial Query Pull").Cells(i, 2).Value
             Sheets("Workable").Cells(j, 3) = Sheets("Initial Query Pull").Cells(i, 10).Value
             Sheets("Workable").Cells(j, 4) = Sheets("Initial Query Pull").Cells(i, 9).Value
             Sheets("Workable").Cells(j, 5) = Sheets("Initial Query Pull").Cells(i, 31).Value
             Sheets("Workable").Cells(j, 6) = Sheets("Initial Query Pull").Cells(i, 5).Value
             Sheets("Workable").Cells(j, 7) = Sheets("Initial Query Pull").Cells(i, 6).Value
             Sheets("Workable").Cells(j, 8) = Sheets("Initial Query Pull").Cells(i, 33).Value
             Sheets("Workable").Cells(j, 9) = Sheets("Initial Query Pull").Cells(i, 8).Value
             j = j + 1
        End If
    Next i


End Sub

 

[DThib]

Got it.
Should have paid attention in Math class.

Miscounted columns.

 

[igold]

Thanks for the update. I am glad you have it working.