Copy Paste Builder

The Copy Paste Builder automates the process of copying and pasting ranges of cells. It’s found here in the VBA Editor:

Or here in the Excel Ribbon:

Use Cases

• Create a list of column headers
• Copy and insert data from one sheet to another
• Paste formats so new data will have specified formatting

How to Use the Copy Paste Builder

To use the Copy Paste Builder, simply enter the copy range and the paste range:

Then you can choose between several options.

Copy or Cut

• When cells are Copied and Pasted the original cells (the ones that were copied) are unchanged.
  • If a formula is copy+pasted, the cell reference in the formula will “move” proportionally, unless they are “locked”.
• When cells are Cut and Pasted the original cells are moved to the paste destination.
  • If a formula is cut+pasted, the cell references will be unchanged.

Click to learn more about cell references.

Paste over Existing Cells or Insert New Cells

• When you select Paste Over Existing Range, cells are pasted into existing cells
• When you select Insert, new cells are created and existing cells are shifted.

Paste Special

Paste Special allows you to copy and paste only certain cell attributes.

For example, when you select Values, if the cell contains a formula, the result of the formula (the cell value) is pasted, not the formula.

You can also Skip blanks, which won’t paste blank values.

Or Transpose which will convert the pasted range from vertical to horizontal (or vis-a-versa).

Start Automating Excel

Get AutoMacro