I've inherited a project. There is an excel workbook for each of 200+ entities unfortunately there were some issues with the originally distributed workbooks. Double-unfortunately, the users have made entries to those workbooks. So we are starting over with fresh workbooks, and need to programatically transfer user-entered data from old (bad) workbook to new workbook. The physical position of the user cells should be exactly the same between old and new.
The user wroksheets have a combination of userinput (yellow) cells and system calculated (not yellow) cells as well as other fields. Since I am new to the project, whatever I do, **I want to make sure I only impact yellow cells**.
I've used indirect referencing formulas in the yellow cells to fetch data from old workbook when it is opened in new workbook via a user form. that part seems to work as expected.
The second step is to reset the indirect reference formulas - this is a one-time operation - leaving only the value, or a blank field. I'm doing this programatically and it works but it is, ummm, not fast.
Although the user worksheets are not uniform with respect to rows, columns, and userinput cells, the all have a grey border around the "user area" (outside this area is hidden system data). And there is a table with metadata for each sheet which contains the top left and bottom right of the grey-bordered area. Which I'm leveraging, but, again, I have to traverse every cell, see if it is yellow, see if it has a formula, if so does the formula contain a certain range name - if so, clear the formula (cell.value = cell.value).
What I'm getting at is, I know the range I'm interested in, but I only want to act on certain cells in that range (per above... yellow, has formula, etc.). I might be able to settle for just yellow.
Any suggestions ? Thank you !
The user wroksheets have a combination of userinput (yellow) cells and system calculated (not yellow) cells as well as other fields. Since I am new to the project, whatever I do, **I want to make sure I only impact yellow cells**.
I've used indirect referencing formulas in the yellow cells to fetch data from old workbook when it is opened in new workbook via a user form. that part seems to work as expected.
The second step is to reset the indirect reference formulas - this is a one-time operation - leaving only the value, or a blank field. I'm doing this programatically and it works but it is, ummm, not fast.
Although the user worksheets are not uniform with respect to rows, columns, and userinput cells, the all have a grey border around the "user area" (outside this area is hidden system data). And there is a table with metadata for each sheet which contains the top left and bottom right of the grey-bordered area. Which I'm leveraging, but, again, I have to traverse every cell, see if it is yellow, see if it has a formula, if so does the formula contain a certain range name - if so, clear the formula (cell.value = cell.value).
What I'm getting at is, I know the range I'm interested in, but I only want to act on certain cells in that range (per above... yellow, has formula, etc.). I might be able to settle for just yellow.
Any suggestions ? Thank you !