Copy Paste Values only( xlPasteValues )

by

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

More Related Contents:

  1. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  2. Get text from clipboard using GetText – avoid error on empty clipboard
  3. Excel macro – paste only non empty cells from one sheet to another
  4. VBA copy rows that meet criteria to another sheet
  5. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  6. How to avoid using Select in Excel VBA
  7. How do I use FileSystemObject in VBA?
  8. Delete row based on partial text
  9. How to put a tooltip on a user-defined function
  10. If WorkSheet(“wsName”) Exists [duplicate]
  11. VBA Shell function in Office 2011 for Mac
  12. How to copy sheets to another workbook using vba?
  13. Check if value exists in column in VBA
  14. hiding formulas in formula bar
  15. How to delete multiple rows without a loop in Excel VBA
  16. VBA Many buttons point to the same _Click sub
  17. Excel VBA Filter Change event handler
  18. How to suppress Update Links warning?
  19. How do we use restful APIs from Excel macros (vba)?
  20. Cannot VBA write data to cells in Excel 2007/2010 within a function
  21. VBA paste as values – how to
  22. How to send email to multiple recipients with addresses stored in Excel?
  23. What are the benefits of using Classes in VBA? [closed]
  24. Writing a VLOOKUP function in vba
  25. Handle cancellation of InputBox to select range
  26. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  27. VBA: How to combine two ranges on different sheets into one, to loop through
  28. Delete all data rows from an Excel table (apart from the first)
  29. Excel Hide/Show all tabs on Ribbon except custom tab
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment