Index & Match Formula & MACRO

[Harry Basra]

I have created a macro that uses a pivot table to extract the a list of reference numbers from a weekly information sheet containing data for the whole country. I use the formula below to match the clients reference numbers within the weekly M.I with the numbers that are relevant to my region. The formula i used is shown below.

=INDEX(PO!A:A,MATCH(Sheet1!A:A,PO!A:A,0))


The problem I'm having is when I receive the new weekly information, I run the Macro and a new pivot table is created in a new sheet. However the formula doesn't recognise that a new sheet has been created. Basically I need the "Sheet1!A:A" part of the formula to be relevant to the new data created by the pivot table every time I run the macro.

Can anyone help please?