Top-office11.ru

IT и мир ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel copy paste

What is the “correct” way to copy-paste data in VBA?

What is the «correct» way to copy-paste data in VBA in Excel?

Now, at least to my knowledge, these three are the only available methods:

Now, I did my research, these are the pros & cons of .Copy([Destination]) and the .Paste([Destination, Link]) method, least those, that I can think of:

  • allows us to paste data in the same expression as copying (code-readability I guess. )
  • Cell references are a complete no go!
  • Your formatting and formulas might get messed up
  • If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks (that’s a huge bummer especially if you’re trying to automize something)
  • Worse yet, if you press Cancel on the prompt, it will throw an Error ** (unless handled)

On the other side of the coin, .PasteSpecial()

  • PasteSpecial() allows us to paste a specific part of range!
  • it allows us to specify what type of paste we want to do
  • has an inbuilt skipBlanks and transpose functionality
  • is not «so» Error -prone!

and I struggled to come up with any, but:

  • a bit more characters to write?

Now, that leads me to believe, that the Destination argument of .Copy() method should essentially be ignored and PasteSpecial() should be always used instead.

Is there ever an occassion where usage of it might be preferable over .PasteSpecial() ? Or should PasteSpecial() be the norm for every copy-paste operation?

1 Answer 1

This isn’t about readability or how many characters you’re typing. It’s about what you need to achieve.

In other words, it’s absolutely not subjective or opinion-based, and like pretty much everything else in programming.

#ItDepends.

If you’re using Excel and copy-pasting cells around, do you Ctrl + C and Ctrl + V or use paste special?

Depends what you need to do.

If you mean to copy a cell, its value, formats, data validations, borders, etc.; then .Paste [Destination] is your best friend. That’s the programmatic equivalent of Ctrl + C / Ctrl + V . Equivalent to PasteSpecial/All, which is overkill since .Paste [Destination] already does everything you need it to do in this case.

Cell references are a complete no go!

Absolutely. Hard-coded cell references are bad. .Paste [Destination] doesn’t force you to do that anyway, so the point is moot.

If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks

Copy and paste ranges cannot overlap, period. You’ll get that prompt through .PasteSpecial too.

If you mean to copy a cell’s Value , but not its formats, data validations, borders, etc.; then .PasteSpecial is definitely a better idea, since that’s the programmatic equivalent of going paste special / values — except it’s probably more efficient to just assign the cell’s Value with what you want (no need to round-trip to/from the clipboard); OTOH if you do mean to paste formats, or data validations, or whatnot, then this is probably the easiest way.

Paste doesn’t «mess up» formats. It does exactly what it means to do. Paste and PasteSpecial are not equivalents. Use the right tool for the job. They are literally the programmatic equivalents of «paste» and «paste special», respectively — if you’re in Excel and systematically going «paste special», you’ll get your stuff done. But every time you do that to «paste all», you’re working harder than you need to be.

PasteSpecial looks like a nice hammer, but not everything is a nail. When you can avoid a clipboard write, it’s generally a good idea to avoid it. but then again, if you’re dealing with huge data sets (think 100K+ cells), it’s possible that it performs better than just assigning the values.

@ScottCraner I did think about it, but that’s not really copy-pasting, but more of a typical pointer referencing, hence I decided not to include it in my question. I didn’t want to open a book to the «what counts as copy-pasting» discussion.

That is wrong. .Range(foo).Value = .Range(bar).Value isn’t «typical pointer referencing». It’s literally taking the values of foo into a 2D variant array, and dumping that 2D variant array onto bar , overwriting the previously held values. As such, it absolutely is a completely valid alternative to hitting the clipboard — but you’ll need to test and compare against Copy + PasteSpecial to see if that’s the best (/most efficient) solution for your situation:

Copy Paste in VBA

Copy Paste in VBA is similar to what we do in excel worksheet, like we can copy a value and paste it to another cell also we can use paste special to paste only the values, similarly in VBA we use the copy method with range property to copy a value from one cell to other and to paste the value we use the worksheet function paste special or paste method.

How to Copy Paste in VBA?

Below are some examples of how to copy paste in excel using VBA.

The basic thing we do in excel is we copy, we cut, and we paste the data from one cell to another cell. It requires no special introduction as well. However, while learning VBA coding is important to understand the same concept in coding language. Copy Paste in VBA is the routine task we do in the day in day in excel. In order to copy first, we need to decide which cell to copy.

Читать еще:  Excel если дата

Example #1 – Copy and Paste Values Using Range Object

Assume you have the word “Excel VBA” in the cell A1.

Let’s say for an example if you want to copy the cell A1 we can use VBA RANGE object.

Code:

The moment you reference the cell we can see all the properties and methods with it. So select the method “Copy”.

Code:

After selecting the method press space key to see the argument of the Copy method.

It says Destination.

This is nothing but where do you want to copy paste values in VBA without selecting the PASTE method.

If we are pasting in the same sheet we can select the cell by using Range object. Let us say if we want to paste the value in B3 cell we can put the destination as “Range(“B3”)”.

Code:

This will copy the data from cell A1 and paste in the cell B3.

We can also use the below method to paste the data.

Code:

First, we will copy & select the data from cell A1 and paste in the cell B3.

Example #2 – Copy to another Worksheet in the Same Workbook

Now if we want to copy paste the value from the different worksheet using VBA macro then in the Destination argument we need to reference the sheet name by using WORKSHEETS object then mention the range of cells in that WORKSHEET. Below code will do the job.

Code:

If we want to copy the data from a particular sheet and want to paste in another particular sheet, we need to mention both the sheets names.

Firstly we need to mention the copying sheet.

Then in the Destination argument, we need to mention the targeted worksheet name and range of the cell.

So the code should like this.

Code:

Example #3 – Copy from One Workbook to another Workbook

We have seen how to copy from worksheet to another worksheet in the same workbook. But we can also do this from one workbook to another workbook.

Take a look at the below code.

Code:

Firstly it will copy the data from the worksheet “Sheet1” in the workbook “Book1.xlsx” from the cell A1.

Then it will activate the workbook “Book 2.xlsx”.

In the active workbook, it will select the worksheet “Sheet 2”

Now in the active sheet, it will paste

Alternative Way for using Copy Paste in VBA

We have one more alternative way of having the data from one cell to another cell. Assume you have the word “Excel VBA” in the cell A1 and you need the same to come in cell B3.

One method we have seen is using the VBA copy and paste method, now I will show you one of the alternative ways. Look at the below piece of code to understand.

Code:

The above says whatever the value is there in the cell A1 should be equal to the value in the cell B3.

Even though this is not a copy and paste method still adds more value to our coding knowledge.

Top Ways of VBA Copy and Paste as Values

Now we will see different ways of VBA copy and paste values. Assume you are in the cell A1 as shown in the below image.

  • If we want to copy and paste we need to reference the cell here, rather we can just use a property of Selection. Copy method.

Code:

OR

  • If you want to copy the entire used range of the worksheet you can use below code.

Code:

This will copy the entire used range in the worksheet “Sheet1” and will paste the same in the worksheet “Sheet2”

Recommended Articles

This has been a guide to VBA Copy Paste. Here we discuss the top ways to Copy and Paste in VBA along with examples & downloadable excel template. Below are some useful excel articles related to VBA –

VBA Copy Paste

VBA Copy Paste

Similar to worksheet function in excel, we can also copy paste data or set of values in VBA. In normal data, we use either CTRL + C to copy a selection of data and then use CTRL + V to paste the selected data in the target cell. But the same in VBA is done by certain codes which we will learn in this article.

How do we use copy and paste in Excel VBA? To do this first we need to activate the target worksheet or workbook from where we want to copy any range of data and that selection is copied by a certain code, when that selection is successfully copied we can go ahead and paste the data in the target cell range.

Читать еще:  Как сделать сложение в excel

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

The syntax to copy the data in VBA is as follows:

Now the syntax to paste the data in other worksheet or any other cell range is as follows:

We use Dot (.) operator to use the copy and paste methods in VBA.

We can copy an entire column and paste it to another column and similarly we can also copy an entire row and paste it to another row. We will learn all these things in this article.

How to Use Excel VBA Copy Paste?

We will learn how to use VBA Copy Paste with few examples in Excel.

VBA Copy Paste – Example #1

For demonstration purpose, I have a random value in cell A1 which I want to copy and paste it to cell B1 Using VBA Code. Have a look below what the data is in cell A1 and cell B1 is blank.

Follow the below steps to use Excel VBA Copy Paste:

Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Once the VB Editor opens up, click on insert and then click on Insert module to insert a code window.

Step 3: Declare a sub-function to start writing the code.

Code:

Step 4: Activate the worksheet first in order to use the properties of the worksheet by the following code.

Code:

Step 5: Copy the data which is in cell A1 by the following code.

Code:

Step 6: Now paste the copied data in the target cell which is cell B1 by the following code.

Code:

Step 7: Run the above code from the run button provided and see the result in cell B1.

VBA Copy Paste – Example #2

I have data in column C and I want to copy the entire data or values and paste it to in column D using VBA code. Have a look below what is the data in Column C and that Column D is empty.

Follow the below steps to use Excel VBA Copy Paste:

Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Click on the module inserted to open up the code window,

Step 3: Declare a sub-function to start writing the code.

Code:

Step 4: Activate the worksheet first with the following code.

Code:

Step 5: Copy the data in column C by the following code.

Code:

Step 6: Now to paste the data in column D use the following code.

Code:

Step 7: Run the following code from the run button provided or press F5.

Run the code to see the following result.

VBA Copy Paste – Example #3

Now for this example, I have a whole bunch of range of Data in cell range G1:H3 and I want to copy the data in the cell range I1:J3. Have a look below at the data I have in the cell range G1:H3 and the cell range I1:J3 is blank.

Follow the below steps to use Excel VBA Copy Paste:

Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Click on the module inserted to open up the code window,

Step 3: Declare a sub-function to start writing the code.

Code:

Step 4: Activate the worksheet first to use its properties by the following code.

Code:

Step 5: Copy the data in the target cell range with the following code.

Code:

Step 6: Now to paste the data in the destination cell use the following code.

Code:

Step 7: Run the above code from the run button provided or press F5 to see the following result.

VBA Copy Paste – Example #4

For this example, I have data in row 10 and I want to paste the data in row 11. Have a look below to see what the data is in row 10 and row 11 is vacant.

Follow the below steps to use Excel VBA Copy Paste:

Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Click on the module inserted to open up the code window,

Step 3: Declare a sub-function to start writing the code.

Code:

Step 4: Activate the worksheet to use the properties of the worksheet.

Code:

Step 5: Copy the row 10 with the following code.

Code:

Step 6: Paste the data of row 10 in row 11 with the following code.

Code:

Step 7: Run the above code by pressing F5 to see the following result.

Things to Remember

  • In order to use data from any worksheet to copy it, we need to activate the worksheet first.
  • Similarly, when we need to paste the data in any other worksheet in VBA we need to activate the destination worksheet first.
  • In case we copy the whole column or row and paste the data in any other column then data anywhere in the row or column in the target cells get copied and pasted to destination cells. This may cause to have some certain unwanted data.
  • The best way to copy the data is by copying a certain range and paste the data in the target cells.
Читать еще:  Как убрать черные рамки в видео

Recommended Articles

This has been a guide to VBA Copy Paste. Here we discussed how to use Excel VBA Copy paste along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Software Development Bundle (600+ Courses, 50+ projects)

Excel VBA: Copy and Paste a Range

One of the most common action you’ll need to learn is copying and pasting a range of data. It’s very easy to do this manually. In normal data, we use either CTRL + C to copy a selection of data and then use CTRL + V to paste the selected data in the target cell. It’s just as easy to copy and paste via VBA.

Range.Copy method

Range.Copy Method is a very convenient Method to copy and paste Range to destination in one line of code. All the formatting and formulas will be copied and pasted.

Syntax

Destination is optional. If this argument is omitted, Microsoft Excel copies the range to the Clipboard. It returns Variant.

Example 1: Copy Range and paste to another worksheet

The below code copy Sheet1 A1 to Sheet2 B1.

If there are more than one Range to copy, you just need to specific the first Range of the destination.

Example 2: copy row

The below code copy from Sheet1 row 1:2 to Sheet2 row 6.

Note that if you copy a row to destination, the destination Range must be a Row or a Range in column A.

Example 3: copy row and paste to new inserted row

Example 4: copy column

The below code copy Sheet1 column A:B to Sheet2 column B:C (because it pastes at B1).

Example 5: copy multiple Range to a new Workbook

In case the copied Columns are not adjacent to each other, Set a Range to combine those Columns (or Rows).

The below code copy range1 and then paste to A1 of new workbook.

Example 6: destination argument omitted

The following code example inspects the value in column D for each row on Sheet1. If the value in column D equals A, the entire row is copied onto SheetA in the next empty row. If the value equals B, the row is copied onto SheetB.

Range.PasteSpecial method

When working with your spreadsheet, you likely often have to copy formulas and paste them as values. To do this in a macro, you can use the PasteSpecial method.

Syntax

It returns Variant.

Parameters

NameRequired/OptionalData typeDescription
PasteOptionalXlPasteTypeSpecifies the part of the range to be pasted.
OperationOptionalXlPasteSpecialOperationSpecifies how numeric data will be calculated with the destinations cells on the worksheet.
SkipBlanksOptionalVariantTrue to have blank cells in the range on the clipboard not be pasted into the destination range. The default value is False.
TransposeOptionalVariantTrue to transpose rows and columns when the range is pasted. The default value is False.
XlPasteType enumeration
NameValueDescription
xlPasteAll-4104Everything will be pasted.
xlPasteAllExceptBorders7Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats14Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme13Everything will be pasted using the source theme.
xlPasteColumnWidths8Copied column width is pasted.
xlPasteComments-4144Comments are pasted.
xlPasteFormats-4122Copied source format is pasted.
xlPasteFormulas-4123Formulas are pasted.
xlPasteFormulasAndNumberFormats11Formulas and Number formats are pasted.
xlPasteValidation6Validations are pasted.
xlPasteValues-4163Values are pasted.
xlPasteValuesAndNumberFormats12Values and Number formats are pasted.
XlPasteSpecialOperation enumeration
NameValueDescription
xlPasteSpecialOperationAdd2Copied data will be added to the value in the destination cell.
xlPasteSpecialOperationDivide5Copied data will divide the value in the destination cell.
xlPasteSpecialOperationMultiply4Copied data will multiply the value in the destination cell.
xlPasteSpecialOperationNone-4142No calculation will be done in the paste operation.
xlPasteSpecialOperationSubtract3Copied data will be subtracted from the value in the destination cell.

Example 1: paste values

Note: To remove the animation around the copied cell add below code:

Example 2: paste formats

Example 3: paste formulas

Example 4: EntireRow copy and paste

Example 5: Multiply

This example replaces the data in cells A1:C2 on Sheet1 with the multiply of the existing contents and cells D1 on Sheet1.

1
2
ABCD
1233
456

VBA Code

Result

1
2
ABCD
3693
121518

Example 6: Add

This example replaces the data in cells D1:D2 on Sheet1 with the sum of the existing contents and cells A1:A2 on Sheet1.

Ссылка на основную публикацию
Adblock
detector