• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Width column paste not working - Selection.Columns.PasteSpecial Paste:=13

S

Serenutty

New Member
Width column paste not working - Selection.Columns.PasteSpecial Paste:=13
I have read many conversations about this subject and the only one that seems to work is the below coding, which widens only 8 columns. Changed 8 to 13 but it doesn't keep width for any of them. Can anyone suggest something that actually works?

Code: 
Selection.Columns.PasteSpecial Paste:=8
ws.UsedRange.Value = ws.UsedRange.Value
Application.CutCopyMode = False  'avoid marching ants

Also tried
'Sheets("Test Page").Cells.Copy
'Sheets("Test Page").Cells.PasteSpecial Paste:=xlPasteValues
'Selection.Columns.PasteSpecial xlPasteColumnWidths
and also .keepSourceTheme or such like
but none of these paste column width

Thanks
 
Did you first copy column width from somewhere? Without first doing that, there really isn't anything to paste.

As well, you only need to specify destination's first cell (top left cell).
Ex: Below worked fine for copying column width for 19 columns (A to S) and pasting column width to T to AL range.
Code: 
Sub Demo()
With Sheet1
    .Range("A:S").Copy
    .Range("T1").PasteSpecial xlPasteColumnWidths
End With
Application.CutCopyMode = False
End Sub
 
Hi Chihiro,
Thanks for your answer. Below is the full code for the copy and paste. I have modified to paste from destination's first cell as you suggest. I've attached the workbook, so you can see what I have done. This code is in module 2, it's very long and the copy and paste is towards the bottom. When running the code directly from the Sub, after a few tries it now pastes maintaining the column width. But I have created a button in sheet "PM-PA Assignment of Personnel" and when I click this button not only doesn't paste column width but it messes up the source sheet column width. I can't understand why as it's the same macro associated with the button. perhaps you can see what I've done wrong?

Also, do you know why I have to click twice sheet "PM-PA Assignment of Personnel" for the buttons to show?
Thanks for your help

Code: 
Sheets("Test Page").Range("A4:AK4").ClearContents

    Sheets("PM-PA Assignment of Personnel").Range("A4:AK4").Copy
    Sheets("Test Page").Range("A4:AK4").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
ws.UsedRange.Value = ws.UsedRange.Value
Selection.Columns.PasteSpecial Paste:=8
Application.CutCopyMode = False  'avoid marching ants
 

Attachments

  • TEST_PAF_Reg-V0 (2).xlsm
    136.6 KB · Views: 4
You are not specifying the correct range to paste widths to:

Code: 
    With Sheets("Test Page").Range("A4:AK4")
    .PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    .PasteSpecial Paste:=xlPasteColumnWidths
End With
 
You must log in or register to reply here.
Back
Top