paste special with widths & formats

**KAPearson** · 05-22-2012, 06:40 AM

Hi could someone please tell me how to paste special with correct widths as well as formats at the same time if possible.
I can copy cells from one sheet to another and get the format ok but how do I get the correct width.
Thanks

Dim rSource As Excel.Range
    Dim rDestination As Excel.Range
    
   
    
    Worksheets("Main").Activate
    Set rSource = ActiveSheet.Range("b:b")

    Selection.AutoFilter Field:=3, Criteria1:="Bakery"
    rSource.Copy
    Worksheets("Main2").Activate
     Set rDestination = ActiveSheet.Range("a:a")
    rDestination.Select

    Selection.PasteSpecial Paste:=xlPasteFormats, _
    operation:=xlNone, _
    skipblanks:=False, _
    Transpose:=False

**tom1977** · 05-22-2012, 07:11 AM

Hi I do not understand your code (part of code?) but if you want keep the width of column and transfer this to another copied column look at such code and try to adjust it.

Sub aa()
Dim x As Long

x = Worksheets("Main").Columns("b").ColumnWidth

Worksheets("Main").Columns("b").Copy Worksheets("Main2").Columns("a")
Worksheets("Main2").Columns("a").ColumnWidth = x

End Sub