Selecting all active sheets

[lizzie0606]

Hello I am trying to copy a format from one sheet to multiply sheets with a macro I am having trouble with one line that would select each sheet. Any suggestions?? Here is what I have so far... The pink is where I keep having errors. This is only my second macro that i've done and i recorded it first then started my modifications. Thanks in advance!!!

Sub Test1()
' Format2 Macro
'Modified to format all sheets without activating
'Sub Macro2 ()
'For each worksheet in the workbook...
For Each sht In Worksheets
sht.Activate
Sheets("Template Long").Select
Cells.Select
Range("B1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets.Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next sht

End Sub

 

[njimack]

Does the below (untested) code work?

Sub Test1()
Dim sht As Worksheet

Sheets("Template Long").Range("B1").Copy

For Each sht In ActiveWorkbook.Sheets
    Cells.PasteSpecial xlPasteFormats
Next sht

End Sub

 

[lenze]

Hi and welcome to the board!!!
If you save your "Template Long" sheet as a one sheet WB as an .xlt, you can simply insert it and its format, text and formulas in to any workbook on demand.

lenze

 

[Norie]

I've got an idea - don't use Select/Activate.

And it might be helpful if you used the loop variable sht.

Perhaps something like this.

Sub Test1()
Dim sht As Worksheet
Dim shtTmp As Worksheet
 
    Set shtTmp = Worksheets("Template Long")
 
    For Each sht In Worksheets
 
        Select Case sht.Name
            Case shtTmp.Name
                'do nothing
            Case Else
                shtTmp.Cells.Copy
                sht.Cells.PasteSpecial Paste:=xlPasteFormats
        End Select
 
    Next sht
 
    Application.CutCopyMode = False
    
End Sub

 

[lizzie0606]

THANKS NEIL this one worked
Sub Test1()
Dim sht As Worksheet

Sheets("Template Long").Range("B1").Copy

For Each sht In ActiveWorkbook.Sheets
Cells.PasteSpecial xlPasteFormats
Next sht

End Sub

Now how would I add in ...

Sheets("Template Long").Select
Columns("O:BB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("O:O").Select
ActiveSheet.Paste
Range("O2").Select

so it would do both...or should i just make another macro to copy over this formula ? Thanks so much for the help!