For a macro to unpivot the source data, go to the Unpivot Excel Data Macro page In this video, you'll see how to locate the pivot table data source, then check the data source, to make sure it includes all the rows and columns that you need.If necessary, adjust the data source, to include new rows or columns.Again, this is the kind of summary that you can easily create using a Pivot Table.So even if you eventually want such a look for your data, maintain the source data in a Pivot ready format and create this view using the Pivot Table.If you change any of the text or numbers in your data set, you need to refresh the pivot table. If you change the size of your data set by adding or deleting rows/columns, you need to update the source data for the pivot table. This way your data source will be updated automatically when you add or delete rows/columns.
After you create a dynamic source that contains the pivot table data, follow these steps, to use that source data: When you create a pivot table in your workbook, the source data records are saved in a special memory area -- a pivot cache.NOTE: For a long term solution, use a named Excel table, as a data source.It will adjust automatically, if new rows are added.How to locate and change the source data for an Excel pivot table.For missing source data, steps to try to recreate it.Sheets("Current Week") Set sh Prior Week = Active Workbook. Sheets("Prior Week") Set sh Pivot = Active Workbook. Sheets("Brand Pivot") Set sh Current Week = Active Workbook. A3: X & lr" End With Dim sh Brand Pivot As Worksheet Dim sh Current Week As Worksheet Dim sh Prior Week As Worksheet Dim sh Pivot As Worksheet Dim lr As Long dim rng as range Set sh Brand Pivot = Active Workbook. Row set rng = shcurrentweek.range("A3: X" & lr) With sh Pivot. This again is an output that can easily be obtained using a Pivot Table. There are blank cells in the data set and the quarters are spread as column headers.Also, the region is specified at the top, while it should be a part of every record.