How do I populate a change on a template across multiple sheets in a workbook?

[PepperPots]

I have an excel spreadsheet with the first sheet that acts as a “template form”. I complete the form, and then run my macro that creates a copy into a new sheet and then clears the data and selections from the template.<o></o>
<o></o>
Every now and then the template needs to be updated and those changes also need to be distributed to the other forms. I have a couple hundred forms within the spreadsheet and I don’t want to update eachsheet manually. How can I go about populating the changes to all of the other forms?

 

[wideboydixon]

Something like this in the template worksheet module might work:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim thisSheet As Long

If MsgBox("Populate this change to all other sheets?", vbYesNo + vbQuestion, "Excel") = vbYes Then
    For thisSheet = 1 To Sheets.Count
        If thisSheet <> Me.Index Then
            Sheets(thisSheet).Range(Target.Address).Value = Target.Value
        End If
    Next thisSheet
End If

End Sub

WBD

 

[PepperPots]

Thanks wideboydixon, but this actually overwrites the information that was in the range that I edited on the template.

I need to be able to maintain the information that was input on the forms that were generated off of the template but I may need to change the text of a question on the template and populate that across each form. I may also need to add rows in between sections of the form and populate that change across each form.

 

[PepperPots]

Correction, I also have an index page that links to each form I create, I don't want the change to be populated on that sheet. How do I omit it from being a target of the change?

 

[wideboydixon]

Your requirement just became a whole lot harder when you started talking about adding rows; that's lots of hard work.

WBD

 

[PepperPots]

I know lol it would also be better to run the "update form" macro on demand, any ideas how to do that? at least this way it covers basic text change which is better than nothing.

 

[wideboydixon]

Again, complexity has exponentially increased. I think I'm out.

WBD

 

[cprice915]

Did you ever get this figured out?