Copying Format and Column Widths from one worksheet to others

[ejrhodes]

Hopefully this is an easy one.

I am trying to copy the column widths and formatting from my first sheet "sheet 1" and paste it into all other sheets in the workbook. My code is as follows. Any suggestions?

Sheets("Sheet1").Range("A:Z").Copy
For Each wsheet In wb.Worksheets
With wsheet
If .Name <> "Sheet1" Then
.Range(Cells(1, 1), Cells(1, LastCol)).PasteSpecial xlPasteFormats
End If
End With
Next

 

[macropod]

Hi ejrhodes,

This works for me:

Sub Test()
Dim wsheet As Worksheet, wb As Workbook
Set wb = ThisWorkbook
With wb
  .Sheets("Sheet1").Range("A:Z").Copy
  For Each wsheet In .Worksheets
    With wsheet
      If .Name <> "Sheet1" Then
        .Range("A1").PasteSpecial xlPasteFormats
      End If
    End With
  Next
End With
End Sub

Cheers
Paul Edstein
[MS MVP - Word]