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
[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CIR[/TD]
[TD]DUE DATE
[/TD]
[TD]TASK1[/TD]
[TD]TASK2[/TD]
[TD]TASK3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111
[/TD]
[TD]1/15/2019[/TD]
[TD]5/15/2018[/TD]
[TD]83[/TD]
[TD]7/30/18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222[/TD]
[TD]2/15/2019[/TD]
[TD]39[/TD]
[TD]8/5/2018[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333[/TD]
[TD]3/1/2019[/TD]
[TD]40[/TD]
[TD]8/1/2018[/TD]
[TD]8/6/2018[/TD]
[/TR]
</tbody>[/TABLE]
11111 (PROJECT DETAILS)
[TABLE="width: 200"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CIR[/TD]
[TD]
[TABLE="width: 200"]
<tbody>[TR]
[TD]11111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE SENT[/TD]
[TD]DAYS-PENDING[/TD]
[TD]DATE-REC'D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DUE DATE[/TD]
[TD]
[TABLE="width: 200"]
<tbody>[TR]
[TD]1/15/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]TASK1[/TD]
[TD]5/1/18[/TD]
[TD]14[/TD]
[TD]5/15/18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TASK2[/TD]
[TD]6/1/18[/TD]
[TD]83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TASK3[/TD]
[TD]7/5/18[/TD]
[TD]25[/TD]
[TD]7/30/18[/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 399px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 200"]
<tbody>[TR]
[TD]11111[/TD]
[/TR]
</tbody>[/TABLE]
</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
[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CIR[/TD]
[TD]DUE DATE
[/TD]
[TD]TASK1[/TD]
[TD]TASK2[/TD]
[TD]TASK3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111
[/TD]
[TD]1/15/2019[/TD]
[TD]5/15/2018[/TD]
[TD]83[/TD]
[TD]7/30/18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222[/TD]
[TD]2/15/2019[/TD]
[TD]39[/TD]
[TD]8/5/2018[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333[/TD]
[TD]3/1/2019[/TD]
[TD]40[/TD]
[TD]8/1/2018[/TD]
[TD]8/6/2018[/TD]
[/TR]
</tbody>[/TABLE]
11111 (PROJECT DETAILS)
[TABLE="width: 200"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CIR[/TD]
[TD]
[TABLE="width: 200"]
<tbody>[TR]
[TD]11111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE SENT[/TD]
[TD]DAYS-PENDING[/TD]
[TD]DATE-REC'D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DUE DATE[/TD]
[TD]
[TABLE="width: 200"]
<tbody>[TR]
[TD]1/15/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]TASK1[/TD]
[TD]5/1/18[/TD]
[TD]14[/TD]
[TD]5/15/18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TASK2[/TD]
[TD]6/1/18[/TD]
[TD]83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TASK3[/TD]
[TD]7/5/18[/TD]
[TD]25[/TD]
[TD]7/30/18[/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 399px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 200"]
<tbody>[TR]
[TD]11111[/TD]
[/TR]
</tbody>[/TABLE]
</body>