asked on

Apply formatting of one Excel worksheet to all BUT TWO worksheets

Experts, please help me with the code in the attached snippet that needs to apply the formatting of the worksheet "Pformat" and apply it to all other worksheets (names will not remain consistent) EXCEPT "Pformat" and "Format Sheets For Printing".

Thank you for any help!!!!

Sub Applyformats()
Dim sh As Worksheet
Worksheets("Pformat").Range("a1:ac1000").Copy
For Each sh In ActiveWorkbook.Sheets
    If sh = "Format Sheets For Printing" Or sh = "Pformat" Then
    GoTo skipp
    Else
    sh.Range("a1:ac1000").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False 'Clear Clipboard
    End If
    
skipp:
    
Next sh
End Sub

Open in new window

jppinto

Just change this:

If sh = "Format Sheets For Printing" Or sh = "Pformat" Then

To this:

If sh.Name = "Format Sheets For Printing" Or sh.Name = "Pformat" Then

adamchicago

ASKER

Thank you very much for the quick response...that fixed that part of the code but now I'm getting a Run-time error '1004' "PasteSpecial method of Range class failed" ?? ...any ideas?

ASKER CERTIFIED SOLUTION