Public Sub my_copy_format()
On Error Resume Next
Const strRange As String = "A1:G10" 'modify range as needed
Const strMaster As String = "Master" 'name of master worksheet
Application.ScreenUpdating = False
'determine current sheet/selection
Set curSel = Selection
Set curWs = ActiveSheet
'copy formats from master worksheet
Worksheets(strMaster).Activate
Range(strRange).Select
Application.CutCopyMode = False
Selection.Copy
'loop through each worksheet and paste formats
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> strMaster Then
ws.Activate
Set wsSel = Selection
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wsSel.Select
End If
Next ws
'restore workbook to original sheet/selection
curWs.Activate
curSel.Select
Application.ScreenUpdating = True
End Sub