Results 1 to 4 of 4

Thread: VBA code needed to copy and paste a row based on a cell reference from another sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

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

    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 by Peter_SSs; Sep 9th, 2018 at 07:11 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by clamont7 View Post
    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
    Code:
    .Range("BZ2").Formula = "=COUNTIF(S5:BI5,'Scheduled Capacity Graph'!B1)"

    Quote Originally Posted by clamont7 View Post
    Thank you so much for all of your help! This is by far the best site for Excel!
    You are welcome .. and I agree.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •