Snasui Excel Forum

Code: Select all

Sub PasteFormatOnly()
Dim r0 As Range, r1 As Range
Dim r2 As Range, rt As Range
Set r0 = Range("C4:H15"): Set r1 = Range("C17:H27")
Set r2 = Range("C29:H39"): Set rt = Range("N4")
Range("N4:S15").ClearFormats
Select Case Range("K6")
    Case 1
        r0.Copy
    Case 2
        r1.Copy
    Case 3
        r2.Copy
End Select
rt.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$K$6" Then
        PasteFormatOnly
    End If
End Sub