Drisso in Vegas
New Member
- Joined
- Aug 23, 2018
- Messages
- 6
Hi there,
I have a workbook that tracks projects by project# (CIR). A button runs a VBA module for NewProjects that prompts for a new (unique number) CIR, copies and pastes a new row into the "Project Summary" sheet, and copies a project details sheet from a template sheet and names it as CIR. So there are multiple "CIR" sheets with the same certain fields that show on the one "Project Summary" sheet. This is done with =INDIRECT formulas in the copied rows of the "Project Summary" sheet like =INDIRECT("'"&$A2&"'!C1").
The problem comes when I update fields in the project details sheets that change from numbers to dates (or vise versa) because the "Project Summary" fields need to be reformatted to or from DATE. I need VBA to update (and reformat) cells in "Project Summary" when the corresponding cells in the "CIR" sheets are changed. It seems like it should be simple but I can't think of the best way ( Do While Loop to find cells by CIR in "Project Summary", Find function, Autofilter?). Can someone point me in the right direction? Thanks.
PROJECT SUMMARY
<tbody>
</tbody>
11111 (PROJECT DETAILS)
<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 399px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody></body>
I have a workbook that tracks projects by project# (CIR). A button runs a VBA module for NewProjects that prompts for a new (unique number) CIR, copies and pastes a new row into the "Project Summary" sheet, and copies a project details sheet from a template sheet and names it as CIR. So there are multiple "CIR" sheets with the same certain fields that show on the one "Project Summary" sheet. This is done with =INDIRECT formulas in the copied rows of the "Project Summary" sheet like =INDIRECT("'"&$A2&"'!C1").
The problem comes when I update fields in the project details sheets that change from numbers to dates (or vise versa) because the "Project Summary" fields need to be reformatted to or from DATE. I need VBA to update (and reformat) cells in "Project Summary" when the corresponding cells in the "CIR" sheets are changed. It seems like it should be simple but I can't think of the best way ( Do While Loop to find cells by CIR in "Project Summary", Find function, Autofilter?). Can someone point me in the right direction? Thanks.
PROJECT SUMMARY
A | B | C | D | E | |
1 | CIR | DUE DATE | TASK1 | TASK2 | TASK3 |
2 | 11111 | 1/15/2019 | 5/15/2018 | 83 | 7/30/18 |
3 | 22222 | 2/15/2019 | 39 | 8/5/2018 | 8 |
4 | 33333 | 3/1/2019 | 40 | 8/1/2018 | 8/6/2018 |
<tbody>
</tbody>
11111 (PROJECT DETAILS)
A | B | C | D | E | F | G | H | |
1 | CIR |
<tbody> </tbody> | DATE SENT | DAYS-PENDING | DATE-REC'D | |||
2 | DUE DATE |
<tbody> </tbody> | TASK1 | 5/1/18 | 14 | 5/15/18 | ||
3 | TASK2 | 6/1/18 | 83 | |||||
4 | TASK3 | 7/5/18 | 25 | 7/30/18 |
<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 399px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
11111 |
<tbody>
</tbody>