Copy several columns width to another 2 sheets

**ruliansyah** · 01-05-2013, 03:26 AM

Hello again,
here's another question from me

I have let say 5 sheets in a workbook and I want to copy columns width from 1 sheet to another 2 sheets, how to do that using macro?? I don't want to use autofit with the code anyway.
Thanx

**smuzoen** · 01-05-2013, 05:21 AM

As an example copy Columns A:E from Sheet1 to Sheet2

Worksheets("Sheet1").Columns("A:E").Copy
    Worksheets("Sheet2").Columns("A:E").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

**ruliansyah** · 01-07-2013, 12:09 AM

Thank you very much smuzoen, that works

finally I make it like follow, but may be you have a better suggestion:

Sub AdjustWidth()
 Dim wsh As Variant
    Application.ScreenUpdating = False
    ActiveSheet.Columns("A:J").Copy
    x = ActiveCell.Address
    t = ActiveSheet.Name
    For Each wsh In Array(Sheet3, Sheet4, Sheet5)
        wsh.Columns("A:J").PasteSpecial Paste:=xlPasteColumnWidths, operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsh.Activate
        ActiveSheet.Range(x).Select
    Next wsh
   
   Sheets(t).Activate
   Application.CutCopyMode = False
   Application.ScreenUpdating = True

End Sub

thanks

**smuzoen** · 01-07-2013, 02:02 AM

The only comment would be is that there is no need to activate or select in the loop above - I cannot see that as being required so you could remove this. Sheets do not have to be active or selected to effect a copy command as long as the correct sheet is referenced which you have done in your code.