[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

0
adamchicago
Asked:
adamchicago
  • 4
  • 2
  • 2
1 Solution
 
jppintoCommented:
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
0
 
adamchicagoAuthor Commented:
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?
0
 
Rory ArchibaldCommented:
Remove line 9 from your code:
Sub Applyformats()
Dim sh As Worksheet
Worksheets("Pformat").Range("a1:ac1000").Copy
For Each sh In ActiveWorkbook.Sheets
    If sh.Name = "Format Sheets For Printing" Or sh.Name = "Pformat" Then
    ' do nothing
    Else
    sh.Range("a1:ac1000").PasteSpecial xlPasteFormats
    End If
    
Next sh
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jppintoCommented:
Remove this line:

Application.CutCopyMode = False 'Clear Clipboard

jppinto
0
 
adamchicagoAuthor Commented:
Thanks
0
 
jppintoCommented:
With all due respect, can you please explain me why you only accepted the solution provided by Rory?
0
 
Rory ArchibaldCommented:
I agree it should be a points split. I'd also love to hear the reason for the B grade.
0
 
jppintoCommented:
That part I didn't noticed! I also don't understand why the B grade!?!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now