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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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:

clamont7

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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:
 

Forum statistics

Threads
1,082,117
Messages
5,363,256
Members
400,723
Latest member
Jsdk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top