I have some routines where I want to use the address of cells on another sheet as reference for a Sum formula in VBA. Here is a simple example: Code: Dim U as range Dim Sht as worksheet Dim NewSht as worksheet Set Set Set U = Union(Sht.range("M24"),Sht.range("M33"),Sht.range("M34")) NewSht.range("G22") = "=sum(" & U.address(1,1) & ")" The result for Cell G22 on C3_Expense_report sheet is: =SUM('C3_Report'!$M$24,$M$33:$M$34) This gives me the sum of cell M24 on C3_Report plus the sum of cells M33 and M34 on Income Expense Summary. I need it to be like this: =SUM('C3_Report'!$M$24,'C3_Report'!$M$33:$M$34) I created a roundabout way to fix it, but maybe there is an easier way. Any suggestions?
This question generated 16 answers. To proceed to the answers, click here.