0

I'm trying to have a range of cells (Installation Agreement: parts of i column) populated with the number in (Assessment : C4) every time I make an adjustment to the "Job Report" sheet. But it's not updating correctly. Any suggestions?

Sub AutoInsertUsage()
ThisWorkbook.Sheets("Job Report").OnEntry = "Usage"
End Sub

Sub Usage()
    With Sheets("Installation Agreement")
        .Range("i47:i72") = Sheets("Assessment").Range("c4")
        .Range("i24:i38") = Sheets("Assessment").Range("c4")
        .Range("i41:i44") = Sheets("Assessment").Range("c4")
        .Range("i10:i11") = Sheets("Assessment").Range("c4")
        End With
End Sub

PS: The i column cells of the Installation Agreement sheet are locked for editing, otherwise I would simply do "='assessment!'C$4$".

user3005651
  • 11
  • 4

1 Answers1

1

Go into the VBA module for the Job Report tab (right-click on the tab and hit "View Code"), then use the Worksheet_Change event as so:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("Installation Agreement")
        .Range("i47:i72") = Sheets("Assessment").Range("c4")
        .Range("i24:i38") = Sheets("Assessment").Range("c4")
        .Range("i41:i44") = Sheets("Assessment").Range("c4")
        .Range("i10:i11") = Sheets("Assessment").Range("c4")
    End With
End Sub

The Target parameter will be populated with the range of cells that were modified, so you can decide from that if the update needs to be done or not.

Joe
  • 6,767
  • 1
  • 16
  • 29
  • 1
    Since you are writing to cells in `Worksheet_Change`. Youmight want to see [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640)? – Siddharth Rout Nov 18 '13 at 18:19