LOOK UP, COPY PASTE into Another Sheet Within SAME Workbook - with TODAY Date

Need help expanding this code to make it do the following:
1. Start w/ "RULE-Table" sheet,
2. Take note of "TODAY" date,
3. LOOKUP date in "RULE-table"
4. Locate Coordinates found in Col D of RULE-Table,
5. LOOKUP the data that resides in those Coordinates on the "PRODUCTION" sheet & COPY
6. PASTE data into "REPORT" tab C2 and E2.

Here's an example to help visualize:
Code will do everything it needs to do within an Undisclosed OPEN file with several sheets.
The reason the Workbook file name is undisclosed/undefined is because the file name will vary based on clients, so I don't want the code to be limited to look for a specific filename.

*The sheet names within this OPEN client file will always have the same names.


The sheet called: "RULE-Table" holds coordinates based upon "TODAY's" computer date.
For example: If today is JAN 4, the desired coordinates to pull data from are "B5" and "D10" from the "PRODUCTION" sheet.


"RULE-Table" sheet
A......B.........C................D.......
Row1.DAY....WK RANGE....QTR RANGE
2......1/1.......1...............B2,D7
3......1/2.......1...............B3,D8
4......1/3.......1...............B4,D9
5......1/4.......1...............B5,D10
6......1/5.......1...............B6,D11

Since today is 1/4, its rules say go to B2 and D10 of the PRODUCTION tab and collect the data

"PRODUCTION" (data source) sheet (within the same open file)
A.......B......C....D....E....F...
Row1..Header..Hdr..Hdr..Hdr..Hdr.
2.......2.................................
3.......14...............................
4.......13...............................
5.......20...............................
6.......62..............................
7....................76.................
8...................184................
9...................375................
10.................410................
11.................525...............

Last, the collected data from those 2 coordinates should always get pasted onto the "REPORT" tab
into C2 and E2 in this example.

"REPORT" (destination) sheet (within the same open file)
A........B.........C.....D....E.....
Row1..Header..Hdr..Hdr..Hdr..
2...................20........410......

Please Login or Register  to view this content.

bump - still looking for help on this

I've got this piece of code that is performing the action of extracting the result and pasting it to the "REPORT" tab, however, it is erroring out with the dreaded "#N/A".... because the formula / function is getting converted/corrupted as it pastes itself into "C2" and "E2"....

Please Login or Register  to view this content.

Does anyone know how to keep it from corrupting?

When I look at the formula in "C2" after it pastes to the "REPORT" sheet, the function/formula converted itself a tiny bit -- here's what it ends up looking like:
=INDIRECT(CONCATENATE("Production!",(LEFT(VLOOKUP(TODAY(),'RULE-Table'!D:F,3,0),FIND(",",VLOOKUP(TODAY(),'RULE-Table'!D:F,3,0),1)-1))))

The same is true for the "E2" paste:
=INDIRECT(CONCATENATE("Production!",(RIGHT(VLOOKUP(TODAY(),'RULE-Table'!D:F,3,0),LEN(VLOOKUP(TODAY(),'RULE-Table'!D:F,3,0))-FIND(",",VLOOKUP(TODAY(),'RULE-Table'!D:F,3,0),1)))))



I've seen this happen before and someone told me if I'll put a "$" in front of the reference, it should keep it from converting...
I attempted that, (I put a dollar sign in front of each of your "C" references) like this:
"=INDIRECT(CONCATENATE(""Production!"",(LEFT(VLOOKUP(TODAY(),'RULE-Table'!$C[-1]:$C[1],3,0),FIND("","",VLOOKUP(TODAY(),'RULE-Table'!$C[-1]:$C[1],3,0),1)-1))))"
Range("E2").Select

...but it did not work, instead it caused it to error out when it gets to that line using "F8" to step through it...
Run Time Error 1004: Application defined or Object defined

Any ideas how to keep it from changing itself???

I think it must have to do with this section: 'RULE-Table'!C[-1]:C[1],3,0),FIND

Can someone tell me what this breaks down to?
Assume C............ = Column C
...........C[-1]...... = Column C minus 1 (which is Column B)
...........C[1]....... = Column C plus 1 (which is Column D)
.......... ,3........ ?
.......... ,0........ ?

Am I interpreting this correctly?