Hello Excellers, welcome to another #macromonday.  Today let’s look at a really useful  VBA macro where I will show you how to copy a variable sized range of cells from one workbook to another.  I often have to copy and paste data, and a lot of times it would not be the same range of cells.  One example for me is copying a Customer List every week.   They often vary in rows especially from week to week.   So, this is one handy macro that you can use over and over again even if your data ranges vary.

Let’ walk through a typical example using a sample dummy data set below. It is a simple customer detail list.  I want to copy this data into an Excel worksheet which is stored on a shared drive which I use as the main Customer List report.  It has all of the formattings and links already set up so I never want to recreate the wheel and do the same thing over and over again when I can get Excel to do all of the hard work for me.

What Does The Macro Do?

This macro will copy a variable range of cells using the CurrentRegion Property.  This returns a Range Object that corresponds to the block of cells around a cell.  In my example report, the range object will correspond to a block of cells around cell A1.  This is where my report always begins.

So, I want my Excel macro to do the following

Open my Customer List file and delete the contents.

Copy my new Customer List data

Paste my new Customer List data to my Customer List file.

How Does The Macro Work?

The macro works in various stages.  First, the Customer List file is opened and the cells that contain data are deleted using the CurrentRegion property. Excel then opens the new file that contains the new customer list and copies any cells that contain data again using the CurrentRegion property.  The data is then pasted into the original Customer List file which is then saved and closed.

You can choose to add a new module to save this code either in the Customer List workbook or in your Personal Macro Workbook.  If you put the code into the Customer List workbook it will only be available in that workbook.  If you want the code or macro to available in all workbooks then you need to create a module and save the code in your Personal Macro Workbook.  To read more about your Personal Macro workbook then check out my other blog posts below.

Macro Mondays – How To Delete Your Personal Macro Workbook

Macro Mondays -Creating and Updating Your Personal Macro Workbook

In this instance, it makes sense to have the code saved in the Customer List workbook as it is very specific to that file.

Step1.  First, we need to declare a variable or three.

• • Dim shtTar As Workbook

(This is my main customer list workbook I use every week)

• • • Dim StartCell As Range

(refers to a range of cells to copy and paste)

• • • Dim shtSource As Workbook

(this is the workbook that contains the new customer details)

Step 2.  Excel opens the Customer List Workbook which contains the customer list details I use as my main report.  Any data on the worksheet is deleted.  (to make way for my updated data).

Step 3.  Excel then opens the file contains the fresh customer details, just ran from the customer system and copies all of the cells that contain data.

Step 4.  My Customer List workbook is then set to the active workbook and the copied data is pasted into the workbook beginning at cell A1.

Step 5.  My Customer List workbook is subsequently then saved and closed

Step 6.  Our routine then ends

Want To Copy The Code?

Sub CopyCustList()

‘Macro by How To Excel At Excel

‘this is my main customer list workbook I use every week

Dim shtTar As Workbook

‘refers to a range of cells to copy and paste
Dim StartCell As Range

‘this is the workbook that contains the new customer details
Dim shtSource As Workbook

 

‘Open my original Customer list
Set shtTar = Workbooks.Open(“C:\Users\BARBARA\Desktop\Customer List.xlsx”)
Set StartCell = Range(“A1”)

‘Select and delete all of the cells contents
StartCell.CurrentRegion.Delete

‘Open my fresh Customer list
Set shtSource = Workbooks.Open(“C:\Users\BARBARA\Desktop\Customer List New.xlsx”)
Set StartCell = Range(“A1”)
‘Copy the current region of cells
StartCell.CurrentRegion.Copy

‘Activate my original customer list
shtTar.Activate
‘paste the copied cells starting at cell A1
StartCell = Range(“A1”).PasteSpecial

‘Close and save my updated Customer List
shtTar.Save
shtTar.Close

End Sub

What Next? Want More Excel Tips?

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

 

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

I am pleased to announce I have teamed up with Excel Rescue,  where you can get help with Excel FAST.