Excel VBA – Copy Range

One of the most common functions I use in Excel VBA is to select and copy a range of cells.  The following examples will illustrate how to select a range of cells, copy them to another location, and then copy and paste a formula to a desired range.

For starters, the following example is based on a simple Excel table:

Excel VBA - Copy Range

So now, let’s say you wanted to copy the contents of range A2:A16 over to column C.  The actual VBA code is quite simple:

Once you step through the macro, your Excel file will look like this:

Excel VBA - Copy Range

As a result of the above, you will want to insert a formula in Range B2, to compare column A to column C.  The following formula is a simple comparison between the contents of two cells, resulting in “Same” or “Different”.

Excel VBA - Copy Range

Furthermore, you would like to copy this formula down in each row to view which cells in the range are different, based on the comparison.  You can use the following code to achieve this:

Once you run the code, your Excel file will look like this:

Excel VBA - Copy Range

At this point, you get the idea.  Selecting ranges of cells or copying cell content to ranges of cells, dynamically, is a very useful technique while writing Excel VBA macros.

You can modify these macros to meet your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment