Copy Rows Within Excel Sheets via VBA

Introduction

I was trying to copy an entire row from one sheet to another sheet using VBA while developing macros for my Excel file. I Googled for a solution and found many tips, but I wanted to use the simplest way to do it. I found that EntireRow property of Range object to be very useful. This tip can be used only with Microsoft Excel 2010 and higher versions.

Background

While developing Macros in Excel, a developer at some point may want to copy an entire row in one sheet to another sheet, you will find that Range object provides many options to do this copying. Many of the options are limiting in some way like you can copy only selected predefined columns, or use awkward syntax notation like in case of Copy method. This tip aims to provide most simplified way of copying an entire row from one sheet to another.

Using the Code

The best way to copy entire rows between two sheets is to use Value property of Range object accessing it via EntireRow property. The code snippet below copies the entire second row in Sheet2 to tenth row in Sheet6.

Sheet6.Rows(10).EntireRow.Value = Sheet2.Rows(2).EntireRow.Value

Points of Interest

I found that intelliSense in Microsoft Excel development environment does not show any property EntireRow or its properties and methods in Microsoft Excel 2010, but code works fine while executing.