Results 1 to 6 of 6

Thread: VBA - Copy Paste using index match to determine paste location
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Copy Paste using index match to determine paste location

    All tabs on same .xls file.

    Tab CurrentCases has named table with active cases, client name, contact info, follow up date, ect.
    Tab Outbound is working space, cell K3 references another cell so it contains the next business day.

    I have a drop down box on Outbound with data validation to CurrentCases with all my active customer names. I click the customer name, and my sheet populates with all the correct info.

    I want to click a button on the Outbound tab and have it copy the date in K3, then replace the current date for the active customer on the CurrentCases tab.

    I can't figure out how to set the paste location using index match. I already have the formula, i just can't get it to work in VBA. This is the formula that works to determine the paste location.

    =INDEX(CurCases[[Name]:[Follow Up]],MATCH(J1,CurCases[Name],0),8)

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy Paste using index match to determine paste location

    Worksheets("Outbound").Range("K3").Copy
    Worksheets("Outbound").Range("INDEX(CurCases[[Name]:[Follow Up]],MATCH(J1,CurCases[Name],0),8)").PasteSpecial Paste:=xlPasteValues

    this is what I want but obviously the syntax is wrong

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy Paste using index match to determine paste location

    Correction:

    Sub SetDate()


    Worksheets("Outbound").Range("K3").Copy
    Worksheets("CurrentCases").Range("INDEX(CurCases[[Name]:[Follow Up]],MATCH(J1,CurCases[Name],0),8)").PasteSpecial Paste:=xlPasteValues


    End Sub


    Wish I could edit my post instead of adding replies

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy Paste using index match to determine paste location

    any ideas?

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy Paste using index match to determine paste location

    bump

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,813
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA - Copy Paste using index match to determine paste location

    Please explain in words what your wanting to do.

    See your showing us how you want it done.

    Just tell me what you want to accomplish and I will write you the script.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

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
  •