Using VBA to link 2 identical workbooks

[EvilUeki]

Basically, I have 2 identical workbooks with multiple sheets. (WorkbookA.xls and WorkbookB.xls) For each sheets there are some cells that are colored yellow within the same range (lets say A1:AA1000).

I would like to loop through every sheet in WorkbookA.xls and select all yellow colored cells, then make the formula for every yellow cells in WorkbookA.xls to be equal to the exact same cell in WorkbookB.xls ( so for example: WorkbookA.xls, sheet1, Cell A2, which is a yellow cell, the formula for it would be =[WorkbookB.xls]sheet1!A2 )

Any good suggestions on how to solve this?
Appreciate any help, thanks.

 

[Fazza]

1. why do you say the code did not loop to the second sheet?

2. I don't understand how the book1 can have values. formulas are created to book2. Are the values NOT there before the code starts, and created directly as values? Is there any other code running somehow?

Not much help, I know. but I am at a loss to understand why the issues are occurring on what is simple code.

Are you able to do some debugging in the VBE?

And I'll be stopping for today very soon.

 

[Fazza]

Maybe strip it back to the basics. Basic yellow. two files both open. just 2 or 3 worksheets in each.
to eliminate other code running, toggle application.enableevents
check the initial values in both files.
step through one line at a time & see what happens

AND, I didn't code for matching yellow ranges in both files, just yellow cells in the file being updated

cheers

 

[EvilUeki]

Sorry...done some debugging and simplified the workbook a bit.... now it pretty much works out fine (i.e. the yellow cells in book1 is now linking to book2 through formula)

The only remaining problem is that..the code is not looping to the second sheet onwards. nothing happened in sheet2 or beyond.

Thank you so much for your help!

 

[Fazza]

1. why do you say the code did not loop to the second sheet?

2. why did the code not work & now it does (partially?)? how did the updated file have values and now has formulas? I think any problems are not with the code if applied to a simple set up. there are other things happening & I don't know about them

Can you test on a simple file and confirm the code works?
And then modify to suit whatever other set up you have?