In this lesson, you find multiple examples of how to copy and paste values with VBA.
Copy row or column between sheets with numbers
The following subroutine copies the first column (A) in the first sheet (“Sheet1”) and pastes it into the first column of the second sheet (“Sheet 2”).
And the second example. This time let’s copy the first row.
Copy row or column between sheets with names
Often using numbers in selecting rows and column may be very inefficient. That’s why using names is usually a better idea. Of course, it does matter for sheets and columns, and not for rows because they use numbers anyway.
Copies the range to the specified range or to the Clipboard. Destination Optional Variant Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard. The following code example copies the formulas in cells A1:D4. About This Tutorial: You will learn several different methods to Copy & Paste and Cut & Paste using a VBA macro. Read the companion tutorial on Value Pasting and PasteSpecial for more advanced copying and pasting options. 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 multiple rows or columns between sheets
An easy way to copy multiple adjacent columns is as follows. You have to use Range. This code copies the first three rows: A, B, and C.
Copy range
Apart from copying rows and column, you can also copy cell ranges.
This code copies cells B2, B3, C2, and C3.
Use a loop to copy (offset)
The following code copies cell from the first three column and pastes them in B, C, E (Offset = 1).
Copy without format (paste special)
So far we have been pasting data with the exact formatting. But sometimes you don’t want to keep the formatting. In order to change this code, we have to do a simple modification.
Copy paste with transpose
In order to transpose the copied values set transpose to true.
Active5 years, 11 months ago
I am trying to write a script which copies a row from Sheet 1 to Sheet 2, if the value for the first column of Sheet 1 is greater or equal to 10.
Community♦
user1296160user1296160
3 Answers
This is similar to the first answer, but a few differences. Here's some notes:
- Use a for-each loop to go through a range (it's not as fast as using a variant array, but keeps things simple and offers better speed than a for loop.
- You may want add a 'If IsNumeric(cell)' check before the value check.
- Don't use select - you don't need to and it wastes resources.
- Better to use the last cell used in A then the used range.
Here is the code:
aevankoaevanko12.9k33 gold badges4545 silver badges5353 bronze badges
Try this: It would be the fastest because it does not depend on selection, but on direct manipulation of data through VBA
Here is a test case I run:
Before
After
aevanko12.9k33 gold badges4545 silver badges5353 bronze badges
ja72ja7219.3k33 gold badges5353 silver badges109109 bronze badges
Siddharth RoutSiddharth Rout122k1515 gold badges163163 silver badges218218 bronze badges