## Auto populate a sheet

**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 data. I´ll show you how to setup 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.

*Example sheet "Data"*

*Example sheet "Pivot table"*

- Select all data on Sheet "Data"
- Go to tab "Insert"
- Click "Pivot table" button
- Click "Pivot table"
- See the remaining steps in this animated gif:

Vba code to refresh pivot table every time the pivot table sheet is activated.

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

- Right click on sheet name "Pivot table"
- Click "View Data"
- Paste code to code module
- Exit VB Editor

*Example sheet "Array formula"*

Array formula in cell A2:

**How to create an array formula**

- Select cell A2
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell A2
- Copy (Ctrl + c)
- Select cell B2
- Paste (Ctrl +v)
- Select cell range A2:B2
- Copy (Ctrl + c)
- Select cell range A3:B15
- Paste (Ctrl +c)

Formula in cell C2:

**How to copy formula in cell C2**

- Select cell C2
- Copy (Ctrl + c)
- Select cell C3:C15
- Paste (Ctrl + v)

Formula in cell D2:

**How to copy formula in cell C2**

- Select cell D2
- Copy (Ctrl + c)
- Select cell D3:D15
- Paste (Ctrl + v)

You can make the "Array formula" sheet auto populating using dynamic named ranges instead of cell references:

Create a dynamic named range in excel

**Download excel *.xlsm file**

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

Follow 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 […]

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 […]

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Pivot table: Count unique distinct records (rows)

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

Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table […]

Sum security holdings monthly and yearly in a pivot table

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 all pivot tables in a sheet

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

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

### 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

Use the img tag, like this: <img src="Insert pic link here">

**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?