## Auto populate a worksheet

**Answer:**

Your question is perfect for a Pivot Table. The problem is that you need to refresh the Pivot Table every time you change the source data, however, this can be done automatically with a few lines of event code.

I´ll show you how to set up the pivot table and how to make it auto refresh using VBA. I will also provide an answer for how to accomplish this using only array formulas.

**What you will learn in this article:**

- Create an Excel defined Table.
- Create a Pivot Table.
- How to set up a Pivot Table.
- Use VBA code in your workbook.
- Save your workbook as a macro-enabled workbook.
- Create array formulas and how they work.

*Example sheet "Data"*

### Create an Excel defined Table

Why convert the data set to an Excel defined Table? It expands automatically meaning you don't need to adjust cell references if you add or delete data.

- Select any cell in your data set.
- Go to tab "Insert".
- Click "Table" button.

- Click the checkbox "My Table has headers" if appropriate.
- Click OK button.

The image below shows what the Excel defined Table probably will look like.

### Create a Pivot Table

- Select all data on Sheet "Data"
- Go to tab "Insert"
- Click "Pivot Table" button
- Click "Pivot table" and a dialog box appears.
- I chose to put the Pivot Table on a new Worksheet.
- Click and hold with left mouse button on fields in Field List.
- Drag to different areas below, now release left mouse button.
- The dates and items go to RowLabels.
- The Price goes to Values area.

Read more about Pivot Tables for more detailed instructions.

The following animated image shows the above steps in greater detail.

*Example sheet "Pivot table"*

Below is VBA code to refresh Pivot Table every time the Pivot Table Sheet is activated. You put the VBA code in a worksheet module, instructions below.

Private Sub Worksheet_Activate() Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable End Sub

### Where to put the code?

- Right click on sheet name "Pivot table"
- Click "View Data".
- Paste code to the worksheet module.

- Exit VB Editor and return to Excel.

### Totals based on Name and Date

The formulas demonstrated here are not necessary if you use a Pivot Table, they simply show how to accomplish the same thing using only formulas. I highly recommend using a Pivot Table.

The image above shows three different formulas, the first formula in cell range B3:C15 extracts unique distinct records based on date and name.

The second formula counts records based on name and date, the third formula adds prices and returns a total also based on date and name.

Array formula in cell B3:

#### How to create an array formula

- Copy formula above.
- Select cell B3.
- Paste formula to cell.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keyboard keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself, they appear automatically if you did the above steps correctly.

#### How to copy array formula

- Select cell B3
- Copy cell. (Short cut keys: Ctrl + c)
- Select cell range B3:C15
- Paste to cell range (Short cut keys: Ctrl +v).

#### Explaining formula in cell B3

The array formula contains cell references (structured references) to the Excel defined Table, there is now no need to adjust cell references if the data source expands or shrinks.

You can find an explanation to the formula here: Filter unique distinct records

### Count records based on conditions

Formula in cell D3:

Copy cell D3 and paste to cell range D3:D15.

#### Explaining formula in cell D3

This formula counts the number of records based on two conditions, name in the corresponding cell in column C and date in the corresponding cell in column B.

This article explains the formula in greater detail: Count unique distinct records

### Sum amounts based on conditions

Formula in cell E3:

This formula is simply a SUMIFS function, read more about it here: How to use the SUMIFS function

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Use hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

To be able to use a Pivot Table the source data you have must be arranged in way that a […]

Count unique distinct records (rows) in a Pivot Table

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

How to calculate totals of stock transactions based on dates

Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

Disable autofit column widths for Pivot table

I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]

### 2 Responses to “Auto populate a worksheet”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I may be complicating so if so I apologize up front! I want to autopopulate tab 2 with select information from tab 1 if "Yes" is inut on tab 2, column A.

I have a lot of data in tab 1 but the actionable items are in tab 2 only if "Yes". I may have 50 rows in tab 1 but only need 3 rows in tab 2 and I don't need all columns in tab 1, only a few.

What is the best way to automate this process? Thank you!

William J. Ryan

Can you provide some example data from both sheets?