Copy layout to all worksheets

[Jean-Paul De Winter]

Hi
I have a workbook with 25 worksheets.
The first sheet is my "template "; all other are based upon this template
The question is:
Whe I chnage something on the template: layout/colour of a cell, columwith,
validation of a range of cells.... I want it to be changed in all sheets.
How can this be done?
Thanks

 

[Peter T]

This should copy formats, but not sure what you mean by "validation of a
range of cells".
Change "Sheet1" to your template name.

Sub Test()
Dim wsTpl As Worksheet, ws As Worksheet, r As Range
Set wsTpl = Worksheets("Sheet1") 'template
Application.ScreenUpdating = False
wsTpl.Cells.Copy
For Each ws In ActiveWorkbook.Worksheets
If Not ws Is wsTpl Then
ws.Activate 'only to enable r.Select later
Set r = ActiveCell
ws.Cells.PasteSpecial Paste:=xlFormats
r.Select 'to deselect whole sheet
End If
Next
wsTpl.Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Regards,
Peter

 

[Tom Ogilvy]

If you select all sheets (group the sheets) and make the change.

Make sure you ungroup before making changes not intended for all sheets.

 

[Jean-Paul De Winter]

great help.... saves hours of time
Thanks

If you select all sheets (group the sheets) and make the change.

Make sure you ungroup before making changes not intended for all sheets.