VBA - Copy Paste using index match to determine paste location

[thekingswolf]

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)

 

[thekingswolf]

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

 

[thekingswolf]

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

 

[thekingswolf]

any ideas?

 

[thekingswolf]

bump

 

[My Aswer Is This]

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.