excel - VBA复制列的宽度

Question

下面的 VBA 代码从源数据表中复制数据并将其粘贴到特定的表中。但是，我还需要它在源数据表上粘贴列的宽度。那可能吗？谢谢你的帮助。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tableName As String
Dim tableRange As Range
Dim TypeOfCosts As String
Application.EnableEvents = False

If Range("X1").Text = "aaaa" Then
    TypeOfCosts = "_bbbb"
ElseIf Range("L3") = "cccc" Then
    TypeOfCosts = "_dddd"
Else
    TypeOfCosts = ""
End If

tableName = Range("Y1").Text & TypeOfCosts & "_Costs"

On Error Resume Next
Set tableRange = Application.Range(tableName)
Debug.Print ThisWorkbook.Names.Count
    If Not (tableRange Is Nothing) And Err = 0 Then
    Range("K9").Resize(10000, 10000).ClearContents
    Range("K9").Resize(10000, 10000).ClearFormats
    tableRange.Copy Destination:=Range("M8")
Else
    Err.Clear
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

Answer 1

如果您的代码按预期执行，则代替

tableRange.Copy Destination:=Range("M8")

你可以写

tableRange.Copy    
With Range("M8")
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues, , False, False
    .PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
End With