Copy & Paste in Excel VBA (copy, pastespecial, resize & offset)
This tutorial shows you how you can copy and paste with VBA.
We use the copy method as well as the pastespecial method.
The copy method in VBA, copies data, as well as formatting, formulas, comments etc.
With the pastespecial method, you have more control over your paste options.
For example, you can paste formatting only – or choose to paste values as well as number formatting.
I also show you how to use the resize property, so you can change the size of the current region before you copy.
This is good for cases where you want to copy the data but exclude the header.
Here we add in OFFSET as well.
The syntax of the Copy method is: Copy([Destination]).
This copies all the contents of the source, including any formulas, comments, and formatting.
If the source range is fixed, this can be used: Range(“A4:E10”).Copy Range(“J4”).
This copies all the cells in the source range, A4:E10 and pastes it starting at cell J4.
To create a variable resized range that copies over any new rows added to the source, the CurrentRegion property is used.
This is used to only copy the values, excluding the formatting, comments, and formulas.
The options for the PasteSpecial method can be shown after pressing SPACE after the PasteSpecial method.
- Paste values
- Range(“J20”).PasteSpecial xlPasteValues
- Paste comments
- Range(“J20”).PasteSpecial xlPasteComments
- Paste values and number formats
- Range(“J20”).PasteSpecial xlPasteValuesAndNumberFormats
TIP: To duplicate a row of code, highlight the row, hold down the CTRL button and drag the row over to where you want the duplicate row to be.
This is helpful when copying a region excluding the headers.
The region can be offset one row down using the Offset property.
This row will appear red initially since the formula is incomplete at this point.
To verify that the address is correct, test it in the Immediate Window:
Pressing ENTER will give the address of the new source after the offset has been done.
If having the extra row is an issue, an additional formula is used to exclude it using the resize property.
To use this: Resize([RowSize], [ColumnSize]).
Since the RowSize will be the actual number of rows excluding the header, we will count the region and deduct 1.
This allows it to be dynamic when additional rows are added.
Merging these into one becomes:
This can again be tested in the Immediate Window:
Pressing ENTER will show that it now excludes the blank row after the current region.
The Copy method is then added.
Since the formula row is too long, this can also be broken down into two rows by using the _ separator:
The dotted copy lines will be seen on the spreadsheet as if manually copying a cell.
To remove this, use: Application.CutCopyMode = False.
The destination where the Paste action was done last will be highlighted gray.
To remove this, jump to another cell by using: Range(“A1”).Select.
This makes cell A1 as the active cell as the final action.
Feel free to Download the Workbook HERE.
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW