VBA – Cut, Copy, Paste from a Macro

About This Tutorial: You will learn several different methods to Copy & Paste and Cut & Paste using a VBA macro.

To use this code: Open the Visual Basic Editor (Alt + F11), Insert a new module (Insert > Module) and copy & paste the desired code into the module.

Copy (Cut) and Paste a Single Cell

This example copies or cuts and pastes a single cell, A1 over to B1:

Copy Selection

If you want to copy the active selection use this:

Copy (Cut) and Paste a Range of Cells

This example copies or cuts and pastes a range of cells, A1:A3 over to B1:B3 :

Copy (Cut) and Paste an Entire Column

This example copies or cuts and pastes an entire column, A over to B:

Copy (Cut) and Paste an Entire Row

This example copies or cuts and pastes an entire row, 1 over to 2:

Copy (Cut) and Paste to Another Worksheet or Workbook

Value Paste

Normally, when you Copy and Paste you Paste all the properties of a cell: formatting, formulas, etc.. Value Pasting allows you to Copy and Paste cells’ values and nothing else. The easiest way to Value Paste in VBA is to define the cell’s value directly:

Paste Special

Paste Special allows you to Copy and Paste specific properties of cells (examples: formats, values, column widths, etc.). It also allows you to perform special paste operations (examples: skip blanks, transpose). We will look at several examples below.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!

<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:


  1. Anupam May 22, 2017 at 6:32 am - Reply

    How to cut paste special values in different sheets?

  2. Anupam May 22, 2017 at 6:32 am - Reply

    Using VBA

    • TheAverageBear August 14, 2017 at 6:49 pm - Reply

      in Excel, when you “Cut”, you can only “Paste”, you can’t “Paste Special”. You would have to Copy/Paste, and then clear the contents of the source range.

  3. Suman Devadiga September 12, 2017 at 12:03 pm - Reply


    I have use below code to copy and paste data in another sheet, however can somebody help to copy data in paste special format in same loop

    basically i need help only for below code, this cell contain formula, rest is ok

    Sheets(“Sheet3”).Cells(s, i).Copy Destination:=Sheets(“Sheet4”).Cells(r, 19)

    For j = 0 To lCount
    For i = 12 To 28

    Sheets(“Sheet3”).Cells(s, 1).Copy Destination:=Sheets(“Sheet4”).Cells(r, 1)
    Sheets(“Sheet3”).Cells(s, 2).Copy Destination:=Sheets(“Sheet4”).Cells(r, 2)
    Sheets(“Sheet3”).Cells(s, 3).Copy Destination:=Sheets(“Sheet4”).Cells(r, 3)
    Sheets(“Sheet3”).Cells(s, 4).Copy Destination:=Sheets(“Sheet4”).Cells(r, 4)
    Sheets(“Sheet3”).Cells(s, 5).Copy Destination:=Sheets(“Sheet4”).Cells(r, 5)
    Sheets(“Sheet3”).Cells(6, i).Copy Destination:=Sheets(“Sheet4”).Cells(r, 17)
    Sheets(“Sheet3”).Cells(6, 29).Copy Destination:=Sheets(“Sheet4”).Cells(r, 18)
    Sheets(“Sheet3”).Cells(s, i).Copy Destination:=Sheets(“Sheet4”).Cells(r, 19)
    Sheets(“Sheet3”).Cells(s, i).Copy Destination:=Sheets(“Sheet4”).Cells(r, 11)
    Sheets(“Sheet3”).Cells(s, 6).Copy Destination:=Sheets(“Sheet4”).Cells(r, 6)
    Sheets(“Sheet3”).Cells(s, 7).Copy Destination:=Sheets(“Sheet4”).Cells(r, 7)
    Sheets(“Sheet3”).Cells(s, 8).Copy Destination:=Sheets(“Sheet4”).Cells(r, 8)
    Sheets(“Sheet3”).Cells(s, 9).Copy Destination:=Sheets(“Sheet4”).Cells(r, 9)
    Sheets(“Sheet3”).Cells(s, 10).Copy Destination:=Sheets(“Sheet4”).Cells(r, 10)

    r = r + 1

    Next i

    s = s + 1

    Next j

    • Steven Rynearson September 28, 2017 at 2:46 pm - Reply

      Record a macro and use paste special. That will show you the syntax.

    • Thiago October 4, 2017 at 8:27 pm - Reply

      Just try to use the method PasteSpecial xlPasteValues

      Sheets(“Sheet3”).Cells(s, i).Copy
      Sheets(“Sheet4).Cells(r,19).PasteSpecial xlPasteValues

  4. Macro Learner December 12, 2017 at 5:05 am - Reply

    What’s the script to copy and paste to the last row (undefined)

  5. FURQAN December 21, 2017 at 11:54 am - Reply

    I need your help in this formula, i like to create a new tab button in vb, which i already did and using this below formula and code. Problem is this, code is not letting formula paste in correct format .see below i need j23 then j24 then j25 and same below as i click new tab button to create new row with all formula which i use in copy row to be pasted in below new row.

    Sub me()
    ‘Copy Row 36, Insert Row 37
    Rows(24).Insert shift:=xlDown
    End Sub

    =IF(COUNTIF(I$4:I21,I21)=1,MAX(J$3:J20)+1,VLOOKUP(I21,I$3:J20,2,0)) ( J20 OK )
    =IF(COUNTIF(I$4:I22,I22)=1,MAX(J$3:J21)+1,VLOOKUP(I22,I$3:J21,2,0)) ( J21 OK )
    =IF(COUNTIF(I$4:I23,I23)=1,MAX(J$3:J22)+1,VLOOKUP(I23,I$3:J22,2,0)) ( J22 OK )
    =IF(COUNTIF(I$4:I24,I24)=1,MAX(J$3:J23)+1,VLOOKUP(I24,I$3:J23,2,0)) ( J23 OK ) ( vb copy this row)
    =IF(COUNTIF(I$4:I25,I25)=1,MAX(J$3:J23)+1,VLOOKUP(I25,I$3:J23,2,0)) ( J23 WRONG )
    =IF(COUNTIF(I$4:I26,I26)=1,MAX(J$3:J23)+1,VLOOKUP(I26,I$3:J23,2,0)) ( J23 WRONG )

    • Steven Rynearson December 21, 2017 at 2:31 pm - Reply

      If you copy+paste without inserting a row that might help. Otherwise you can use the formulaR1C1 property to define the formula from within VBA.

  6. Mohamed December 28, 2017 at 2:31 pm - Reply

    Thanks very helpful

Leave A Comment