Results 1 to 7 of 7

Thread: A tuff one, Copy and paste if criteria is partial met
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A tuff one, Copy and paste if criteria is partial met

    Hi

    Is itpossible to create a code that can copy rows from a data sheet and paste to areport sheet if there is a partial match?

    Data is a staff record with data in 11 columns and about 450 rows. In column 1 (A) are names with first- middle- and last name in the same cell. For example, if you are looking for John, all persons with first name John should be copied and paste to the report sheet, the same e.g. last name Smith.

    For each new search, the previous search in the report sheet must be deleted.

    The data inthe report sheet should start from row 3 and Down

    I would appreciate if anyone can help me.

    Thanks inadvance
    Regards
    Jorgen
    Last edited by JorgenKjer; Aug 24th, 2019 at 11:55 AM.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,454
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    What you are asking for is possible. You can improve your chances of getting some help by posting some sample data in a form that can be copied from a browser and pasted to Excel. See this link for how to do this: https://www.mrexcel.com/forum/about-...tachments.html
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    Last edited by JorgenKjer; Aug 24th, 2019 at 02:12 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    Do you need code? This can easily be done with a formula. In A4 copied down & across
    ABCD
    1er< <<   search criteria
    2
    3NamePhoneEmployee #Department
    4Peter Smith246823452002A
    5John Fitzgerald Kennedy246834563003A
    6Peter Jensen246845674004A

    Report



    Worksheet Formulas
    CellFormula
    A4=IFERROR(INDEX(Data!A$2:A$5,AGGREGATE(15,6,(ROW(Data!A$2:A$5)-ROW(Data!A$2)+1)/(ISNUMBER(SEARCH($A$1,Data!$A$2:$A$5))),ROWS($A$1:$A1))),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    Or if you do want a macro, try
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "A1" Then
          Range("A3").CurrentRegion.Offset(1).ClearContents
          With Sheets("Data")
             .Range("A1:D1").AutoFilter 1, "*" & Target & "*"
             .AutoFilter.Range.Offset(1).Copy Me.Range("A4")
             .AutoFilterMode = False
          End With
       End If
    End Sub
    This needs to go in the Report sheets code module.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    Hi Fluff
    Thanks for your prompt reply. The code works perfectly
    Regards
    Jorgen

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: A tuff one, Copy and paste if criteria is partial met

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •