How to Copy a Row, Insert Two Below, and Paste Data in Excel with VBA

Описание: Discover how to efficiently use Excel VBA to copy a row of data, insert new rows beneath it, and paste the copied data into those rows. Get step-by-step guidance and code examples to simplify your workflow!
---
This video is based on the question https://stackoverflow.com/q/65890535/ asked by the user 'deadxcell' ( https://stackoverflow.com/u/15078915/ ) and on the answer https://stackoverflow.com/a/65890805/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to copy row, insert tow under then paste data?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering Excel VBA: How to Copy a Row, Insert Two Below, and Paste Data

Are you struggling with how to efficiently manage rows of data in Excel? If you have a long dataset and you want to make changes based on specific criteria, this guide is just for you! We'll walk through a practical solution using Visual Basic for Applications (VBA) to automate the process of copying rows, inserting new ones below, and pasting data.

Understanding the Problem

Let's set the scene: You have a dataset with multiple rows, and changes occur in one of your columns. For instance, suppose we are analyzing student scores across different subjects. You notice that when there is a change in the subjects (let’s say, From Sally to Chase), you need to insert a blank row below that change to make it clear. Furthermore, you'd like to copy the last row's data before that change and paste it into the newly inserted blank row.

Before and After Example

Before:

Column AColumn BColumn CColumn E12Sally512Sally612Sally212Chase112Chase412Ben9After Running the Code:

Column AColumn BColumn CColumn E12Sally512Sally612Sally22Sally12Chase112Chase42Chase12Ben92BenOur Solution: VBA Code

Now, let's take a closer look at the VBA code that can help you achieve this.

The VBA Code Breakdown

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Setup Variables:

We define a worksheet variable sh that points to the active sheet, and also get the last row of data in column A.

Optimization Settings:

To improve performance, we disable screen updating and change calculation to manual.

Loop Through the Rows:

We start from the last row and move upwards (it’s crucial since you’re inserting rows).

When the value in Column C changes, a new row is inserted.

Copy and Paste Data:

The code duplicates the values from the last relevant row into the newly created row.

Restore Settings:

Finally, we turn screen updating and calculation back to their original states, showing a message box that the operation is complete.

Final Thoughts

By utilizing VBA in Excel, you can enhance your efficiency and automate routine tasks such as copying and pasting rows of data. Whether dealing with educational scores, sales data, or any data analysis, this method can save you significant time and effort.

Give this code a try and feel the difference in how you handle your data in Excel!