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

PepperPots

New Member
Joined
Jun 14, 2018
Messages
12
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:p></o:p>
<o:p></o:p>
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Something like this in the template worksheet module might work:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Your requirement just became a whole lot harder when you started talking about adding rows; that's lots of hard work.

WBD
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top