VBA code needed to copy and paste a row based on a cell reference from another sheet

[clamont7]

I've been trying to write VBA code to copy and paste an entire row's data to a new sheet if a criteria is met. My data is on the "Job Log" sheet and starts in row 5 (row 4 is headers). The criteria selection is on B1 of "Scheduled Capacity Graph" sheet.

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, and that job's entire row would be pasted to the "List from Sch Cap Graph" sheet.

I would like to paste all qualifying jobs on a sheet called "List from Sch Cap Graph" starting in row 5. Any help with this would be greatly appreciated!

 

[Peter_SSs]

There is a fair bit unknown here so this code will unlikely be exactly what you want, but hopefully will get us headed in the right direction.

I have assumed ..
- Data in Job Log sheet actually starts in column A
- Number of rows used in Job Log can be determined from column A
- Column BZ in Job Log can be used as a helper
- You want relevant rows and headers copied from Job Log to List from Sch Cap Graph
- List from Sch Cap Graph contains no data below row 4 before the code is run

If any of those assumptions is incorrect, can you please add further detail, otherwise, try this in a copy of your workbook.

Sub Copy_Rows()
  With Sheets("Job Log")
    .Range("BZ2").Formula = "=COUNTIF(S5:BI5,'Scheduled Capacity Graph'!B1)"
    .Range("A4", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 61).AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=.Range("BZ1:BZ2"), CopyToRange:=Sheets("List from Sch Cap Graph").Range("A5"), Unique:=False
    .Range("BZ2").ClearContents
  End With
End Sub

 

[clamont7]

This actually seems to be working as intended. All of the assumptions you listed are correct. I'm not sure what you mean about column BZ being used as a helper, but I'm guessing you picked that cell because it is blank. Thank you so much for all of your help! This is by far the best site for Excel!

 

[Peter_SSs]

I'm not sure what you mean about column BZ being used as a helper, but I'm guessing you picked that cell because it is blank.

Yes, my code temporarily puts a formula in cell BZ2 to help get the result

.Range("BZ2").Formula = "=COUNTIF(S5:BI5,'Scheduled Capacity Graph'!B1)"

Thank you so much for all of your help! This is by far the best site for Excel!

You are welcome .. and I agree.