VBA copy and paste qualifying rows based on 2 criteria from a seperate sheet

[clamont7]

Hey everyone,

I have a master list that I'd like to create a list of jobs from on a separate worksheet depending on a couple criteria. Here's the logic I'm looking for:

IF "Job Log" (sheet name) Column S (current operation) is equal to the selected criteria on "Scheduled Capacity Graph" (sheet name) cell $B$1

AND

IF "Job Log" (sheet name) Column F (suggested start week) is equal to the date on "Scheduled Capacity Graph" (sheet name) cell $B$3 (start week date)

THEN

Copy entire qualifying row and paste to a separate worksheet named "List from Sch Cap Graph"

LOOP until all qualifying rows have been pasted to the new sheet

Any help with this would be greatly appreciated! Thanks!

 

[clamont7]

So many views and no answer, I thought this would be a simple one for people proficient in VBA

 

[JoeMo]

Are there any formulas in cols F or S of the Job Log sheet?
What are the start rows for data in those two columns?

 

[clamont7]

There are formula's in Column F that calculate the suggested start day [=WORKDAY([@[Due Date]],-([@[Tot Est Machining Time (hrs)]]/10)-6)-WEEKDAY(WORKDAY([@[Due Date]],-([@[Tot Est Machining Time (hrs)]]/10)-6)-2)]

There aren't any formulas in Column S, just text. The start row on the Job Log sheet is 5

 

[JoeMo]

There are formula's in Column F that calculate the suggested start day [=WORKDAY([@[Due Date]],-([@[Tot Est Machining Time (hrs)]]/10)-6)-WEEKDAY(WORKDAY([@[Due Date]],-([@[Tot Est Machining Time (hrs)]]/10)-6)-2)]

There aren't any formulas in Column S, just text. The start row on the Job Log sheet is 5

I'll assume row 5 is where the data starts. Row 4 has headers, if any. Change code to suit. This code avoids having to loop through all the cells in cols F & S on the Job Log sheet which should minimize run time if you have a large number of data rows. And, there is only a single copy/paste operation.

Sub clamont7()
Dim SLog As Worksheet, SCap As Worksheet, vS As Variant
Dim Rs As Range, cs As Range, Rcopy As Range
Set SLog = Sheets("Job Log")
Set SCap = Sheets("Scheduled Capacity Graph")
With SLog.Range("S5:S" & SLog.Cells(Rows.Count, "S").End(xlUp).Row)
    vS = .Value
    .Replace SCap.Range("B1").Value, "#N/A"
    On Error Resume Next
    Set Rs = .SpecialCells(xlCellTypeConstants, xlErrors)
    If Rs Is Nothing Then
        MsgBox "No col S match to criteria - exiting sub"
        Exit Sub
    End If
    On Error GoTo 0
    .Value = vS
End With
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("List from Sch Cap Graph").Delete
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "List from Sch Cap Graph"
    For Each cs In Rs.Cells
        If Cells(cs.Row, "F") = SCap.Range(B3).Value Then
            If Rcopy Is Nothing Then
                Set Rcopy = SLog.Rows(cs.Row)
            Else
                Set Rcopy = Union(Rcopy, SLog.Rows(cs.Row))
            End If
        End If
    Next cs
Rcopy.Copy Sheets("List from Sch Cap Graph").Range("A1")
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[JoeMo]

Apologies, found an error, in case there's no col F match to the criteria. Please use this version:

Sub clamont7()
Dim SLog As Worksheet, SCap As Worksheet, vS As Variant
Dim Rs As Range, cs As Range, Rcopy As Range
Set SLog = Sheets("Job Log")
Set SCap = Sheets("Scheduled Capacity Graph")
With SLog.Range("S5:S" & SLog.Cells(Rows.Count, "S").End(xlUp).Row)
    vS = .Value
    .Replace SCap.Range("B1").Value, "#N/A"
    On Error Resume Next
    Set Rs = .SpecialCells(xlCellTypeConstants, xlErrors)
    If Rs Is Nothing Then
        MsgBox "No col S match to criteria - exiting sub"
        Exit Sub
    End If
    On Error GoTo 0
    .Value = vS
End With
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("List from Sch Cap Graph").Delete
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "List from Sch Cap Graph"
    For Each cs In Rs.Cells
        If SLog.Cells(cs.Row, "F") = SCap.Range("B3").Value Then
            If Rcopy Is Nothing Then
                Set Rcopy = SLog.Rows(cs.Row)
            Else
                Set Rcopy = Union(Rcopy, SLog.Rows(cs.Row))
            End If
        End If
    Next cs
If Not Rcopy Is Nothing Then
    Rcopy.Copy Sheets("List from Sch Cap Graph").Range("A1")
Else
    MsgBox "No col F match to criteria - exiting sub"
    Exit Sub
End If
    
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[clamont7]

Thank you so much for all of the help! I was trying to modify the code, but I'm pretty new to VBA and couldn't figure it out. Is there a way to modify the code so that if the qualifier in B1 of "Scheduled Capacity Graph" is found anywhere in a job's row, then it's row is copied and pasted to the "List from Sch Cap Graph", without referencing the date qualifier on B3 of "Scheduled Capacity Graph"

Basically, each job on the "Job Log" sheet has a list of operations it goes through starting in Column S and going to Column BI. So if the operation found in B1 of "Scheduled Capacity Graph" is anywhere in columns S to BI (on the Job Log sheet), then it qualifies. The assumptions you made about the headers in row 4 and the data starting in row 5 are correct. Thanks again!!!