Consolidating from multiple workbooks - copying final data, not formulas

[BradPhillips]

I've been working on this macro to consolidate multiple workbooks, everyone on here has helped me a lot. I have one last thing (hopefully) that I can't seem to figure out.

In the source workbooks, I'm only copying the data from 1 sheet, however, that data references a table in a separate sheet in the same source workbook. When I copy the data, the formula that looks up the data in the table is copied and just shows a "N/A". Instead, I would just like the final values as once I consolidate the data, I no longer need the connection to the table, just the data.

This is the code I'm using

Please Login or Register  to view this content.

The formula I'm using in the source workbook to look up the table values is

Please Login or Register  to view this content.

I actually have another cell (W10) on the same source worksheet that references another table on the same (different) worksheet as the formula above. If that first formula (the one below) returns "PCPM" then the first formula above looks up a value on the second table. This first formula (below) seems to consolidate to the master workbook just fine.

Please Login or Register  to view this content.

Any ideas?

[AlvaroSiza]

If I'm reading this right, you just need values in the wsMaster, correct? If so, make a copy of your workbook and test the following:

Amend:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[BradPhillips]

I tried your suggestion, but it gives me a syntax error and highlights that part of the code in red.

Please Login or Register  to view this content.