Copy method
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.
Range(“A4”).CurrentRegion.Copy Range(“J4”)
Featured Course
Unlock Excel VBA & Excel Macros
PasteSpecial method
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(“A4”).CurrentRegion.Copy
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.
Resize property
This is helpful when copying a region excluding the headers.
The region can be offset one row down using the Offset property.
Range(“A4”).CurrentRegion.Offset(1,0)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:
?Range(“A4”).CurrentRegion.Offset(1,0).AddressPressing 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.
Resize(Range(“A4”).CurrentRegion.Rows.Count-1)Merging these into one becomes:
Range(“A4”).CurrentRegion.Offset(1,0).Resize(Range(“A4”).CurrentRegion.Rows.Count-1)This can again be tested in the Immediate Window:
?Range(“A4”).CurrentRegion.Offset(1,0).Resize(Range(“A4”).CurrentRegion.Rows.Count-1).AddressPressing 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:
Range(“A4”).CurrentRegion.Offset(1,0)._
Resize(Range(“A4”).CurrentRegion.Rows.Count-1).Copy
Range(“A20”).PasteSpecial xlPasteValuesAndNumberFormats
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.
Summary
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
