Copy Paste Values Only( XlPasteValues )
Answer : If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this: Sub CopyCol() Sheets("Sheet1").Columns(1).Copy Sheets("Sheet2").Columns(2).PasteSpecial xlPasteValues End Sub Or Sub CopyCol() Sheets("Sheet1").Columns("A").Copy Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues End Sub Or if you want to keep the loop Public Sub CopyrangeA() Dim firstrowDB As Long, lastrow As Long Dim arr1, arr2, i As Integer firstrowDB = 1 arr1 = Array("BJ", "BK") arr2 = Array("A", "B") For i = LBound(arr1) To UBound(arr1) Sheets("Sheet1").Columns(arr1(i)).Copy Sheets("Sheet2").Columns(arr2(i)).PasteSpecial xlPasteValues Next Application.CutCopyMode = False End Sub since you only want values copied, you can pass the values of arr1 directly to arr2 and avoid c