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:

6 Comments

  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

    Hello

    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)

    Quote
    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
    Unquote

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

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

    • 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

Leave A Comment