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

clamont7

New Member
Joined
Aug 31, 2018
Messages
15
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
Rich (BB code):
.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. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top