The VBA code below copies the data from a source data sheet and pastes it onto a specific sheet. However, I need it to also paste the width of the columns on the source data sheet. Would that be possible? thanks for any help.
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
If your code is executing as desired then instead of
tableRange.Copy Destination:=Range("M8")
you may write
tableRange.Copy
With Range("M8")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With