How to copy a value with this code and get the right value

[Romano_odK]

Good morning,
I am using this code to copy values from one column to another which fine by itself. Now I running to the problem that I want to copy a column with as value a number to a column that are percentages. This results in huge percentages. Is there a way to fix this.

Private Sub UpdateKorting_Click()
With Sheets("Items").ListObjects("ItemsImport")
   .ListColumns(81).DataBodyRange.Copy
   .ListColumns(50).DataBodyRange.PasteSpecial xlPasteValues, , True
   Application.CutCopyMode = False
Range("AX6").Activate
ActiveWindow.ScrollRow = 6
End With
End Sub

 

[skillilea]

here are a couple ways. If you can have formulas use #2

Private Sub UpdateKorting_Click()
    Dim ar As Variant
    Dim i As Long
    
    Application.ScreenUpdating = False
    With Sheets("Items").ListObjects("ItemsImport")
       ar = .ListColumns(1).DataBodyRange.Value  ' change your column #
       .ListColumns(2).DataBodyRange.NumberFormat = "0.00%"  '  change your column #
       For i = 1 To UBound(ar)
        ar(i, 1) = ar(i, 1) / 100
       Next
       .ListColumns(2).DataBodyRange.Value = ar  ' change your column #
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub UpdateKorting_Click2()
    Application.ScreenUpdating = False
    With Sheets("Items").ListObjects("ItemsImport")
       .ListColumns(2).DataBodyRange.NumberFormat = "0.00%" ' change your column #
       .ListColumns(2).DataBodyRange.Formula = "=[@col1]/100" ' name of your column &  change your column #
    End With
    Application.ScreenUpdating = True
End Sub

 

[Romano_odK]

here are a couple ways. If you can have formulas use #2

Private Sub UpdateKorting_Click()
    Dim ar As Variant
    Dim i As Long
   
    Application.ScreenUpdating = False
    With Sheets("Items").ListObjects("ItemsImport")
       ar = .ListColumns(1).DataBodyRange.Value  ' change your column #
       .ListColumns(2).DataBodyRange.NumberFormat = "0.00%"  '  change your column #
       For i = 1 To UBound(ar)
        ar(i, 1) = ar(i, 1) / 100
       Next
       .ListColumns(2).DataBodyRange.Value = ar  ' change your column #
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub UpdateKorting_Click2()
    Application.ScreenUpdating = False
    With Sheets("Items").ListObjects("ItemsImport")
       .ListColumns(2).DataBodyRange.NumberFormat = "0.00%" ' change your column #
       .ListColumns(2).DataBodyRange.Formula = "=[@col1]/100" ' name of your column &  change your column #
    End With
    Application.ScreenUpdating = True
End Sub

Thank yoy, this works great.

 

[skillilea]

Glad to help