copy data from a sheet in one workbook to a sheet with the same name in another workbook

[ChrisBester]

Dear all,

I created a macro that open a second workbook when I open the first workbook. Both contains a "Contacts" worksheet that I reference in a few places. Both contact list are needed but I only maintain the one in the first workbook and then want to update the same sheet in the second workbook everytime I start the first one.
At first I simply deleted the sheet in the second workbook; " WBOpen1.Sheets("Contacts").Delete " and then copy the same sheet from the fist workbook;
" WBOpen.Sheets("Contacts").Copy After:=WBOpen1.Sheets(10) " . This works fine but then all the formulas in the second workbook that reference this worksheet loses it reference;
for instance this formula " =IFERROR(VLOOKUP(D6,Contacts!$B$4:$G$278,5,FALSE),"------") " becomes this " =IFERROR(VLOOKUP(D6,#REF!,5,FALSE),"------") "

Any suggestion will be highly appreciated

[antoka05]

When you delete a sheet all formulas pointing at that sheet goes in error... you could try to clear data and copy new data without delete the sheet.
Try to change your code with:

Please Login or Register  to view this content.

Regards,
Antonio

[ChrisBester]

Hi Antonio
thanx for that, it looks neat and will definately work, in fact I'm going to tr it as well. I did however tried with the code below and it also seems to solve my problem.

Please Login or Register  to view this content.