Macros in Excel : Pivot Table with Dynamic Data Range

One common issue while creating / recording a macro for Pivot table is the data range of Pivot table gets recorded. Next time when you use the same macro data range for the pivot table will be same as it was at the time of recording of macro. If you do not notice this the report created by macro may represent wrong picture. This may be due to increase in the data size.

However there is a way to overcome this problem by using dynamic data range with the help of Excel Offset Function. Before creating the pivot by macro you need to create dynamic data range using offset function.

Then you create a Pivot with the named range.

See the macro code given below for better understanding

Sub Pivot_with_Dynamic_range()
' This creates Dynamic data range named "PvtData"

ActiveWorkbook.Names.Add Name:="PvtData", RefersToR1C1:= _
"=OFFSET('Data'!R2C1,0,0,COUNTA('Data'!C1),COUNTA('Data'!R2))"

' This creates Pivot using Dynamic data range named "PvtData"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"PvtData").CreatePivotTable TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False

End Sub

The above code handles the major issue of dynamic data range and will create blank pivot table for your data, you need to add the required fields to complete your report. In this data is available at Sheet named "Data" and starting point of the data is Row 2 Column 1 or Cell "A2". You will need to change these references while using the above code in your macro.

Macro Pivot Table , Pivottable VBA , Pivot Table VBA , Pivot Table Macro , Dynamic Pivot Table , Pivot Table Dynamic Range

Macros in Excel : Fill Blank Cells With Data Above

In case you need to fill the blank cells within a range with the data above it , use following macro code. Since this works on pre selected range of data, so make sure that you have selected the required data range before running your macro.

Let us take an example. if you want to fill the blank cells with in range "A2:D10", make sure that you have select this range before running the macro.

Sub Fill_Blank_Cells()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

End Sub 

Please also refer to earlier post on "How to fill blank cells in excel data range with cells above it"

Macros in Excel : Find Blank Cells Macro

In case you need to select blank cells within range , use following macro code. Since this works on pre selected range of data, so make sure that you have selected the required data range before running your macro.

Let us take an example. if you want to find blank cells with in "A2:D10", make sure that you have select this range before running the macro.

Sub Find_Blank_Cells()

    Selection.SpecialCells(xlCellTypeBlanks).Select

End Sub

Macros in Excel : Learn Excel VBA : Objects

It is important to understand Visual Basic Grammar to better understand the macros recorded by you. If you are new to Macros in excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)


Visual Basic is object oriented language, it means all items in Excel are considered as objects. There are lots of them but following example will help us understand what an Object is.

   - WorkBook
   - WorkSheet
   - Range
   - Chart
   - Legend

In a Macro Range("A1") is an object. An object can contain other objects.
WorkBook is the larger object followed by WorkSheet and Range.

Workbooks("Macros Book.xls").Worksheets("Data").Range("A1")

Code above refers to Cell "A1" on worksheet "Data" in "Macros Book.xls" file. This is long description but can easily be shortened. The worksheet containing the cursor is called Activesheet, similarly workbook containing active sheet is called Activeworkbook

ActiveWorkbook.ActiveSheet.Range("A1")

There is no difference in the previous line and line above in your macro code.

In case you do not refere larger object, Excel Macro will use ActiveWorkBook and ActiveSheet. So if you write Range("A1") it means same if you have your cursor at Worksheet "Data" in "Macros Book.xls" file while running this code.

Table below will explain the use of various objects on Excel VBA

WorkBooks - Referes to all workbooks currently open in Excel
WorkBooks.Item(1) - Referes to first workbook
WorkBooks.(1) - Referes to first workbook
WorkBooks.(Macros Book.xls) - Referes to Macros Book.xls file open

Sheets - Referes to all sheets in workbook both chart sheets and worksheets
Sheets(1) - Referes First sheet on tab bar.
Sheets("Data") - Refer to Sheet called Data

Write Macros in Excel / Clean or Tweak Excel Macro

I am going to cover how to Write excel macro / Clean or tweak recorded excel macro. Those who are not aware about recording excel macro , I suggest them to go through my earlier post on

1. Recording Excel Macro

When you record a macro it records many actions which are actually not required. Pls go through the macro code given below recorded by record macro function of excel.

Sub MergeCells()
    Range("B4:C4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
End Sub

This code is doing three actions

1.Select dells in range B4:C4
2.Merge selected cells i.e. B4:C4
3.Unmerge selected cells i.e. B4:C4

If you see there is lots of unwanted action recorded by macro recorder. Similar action can be achieved by cleaning up the unwanted stuff. See the code given below which performs the same action.

Sub MergeCells_clean()
    Range("B4:C4").Select
    Selection.Merge
    Selection.UnMerge
End Sub

Once you have recorded your macro , you can lookout for unwanted stuff recorded by it. Cleaning up that will help you understand your macro better and you will be able to maintain your macro for long time.

Similar action can be achieved by two line code as under :-

Sub MergeCells_simple()
    Range("B4:C4").Merge
    Range("B4:C4").UnMerge
End Sub

Macros in Excel : Making your macro run every time

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Certain precautions while recording a macro will help you generate a code which will run every time you are using that macro.

One major issue when you download data from source system for further processing is the sheet name. It is different every time you download data. Since the macro recorded by you will record the sheet name as available at the time of recording of macro. Same macro will not work next time as the sheet name for downloaded data will be different.

To make it work you need to change the sheet name to generic name as first step while recording your macro.

See the code below which is recorded to change the sheet name as first step. This helps to record the same name in macro in the next step when you flip through the sheets.

Sub ChangesheetName()
    Sheets("Sheet1").Name = "Data"
    Sheets("Data").Select
End Sub

Once you have recorded above macro, small tweaking will help you to run this macro every time irrespective of the sheet name in downloaded data. You need to go to Visual Basic Editor and change the recorded macro as under

A small change in the first line of the code will help you run the macro without worrying about the sheet name of downloaded data.

Sub ChangesheetName()
    ActiveSheet.Name = "Data"
    Sheets("Data").Select
End Sub

Recording Excel Macro / Writing Excel Macro (VBA)

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

If you are recording macros for the first time make you "Visual Basic" toolbar visible as it comes handy for recording.

You need to go to "View -> Toolbars -> Visual basic" Once you have made it visible it will look like as seen in picture below:-

Once you have this visible you are ready to record a excel macro.
Click on to start recording.
Once you have clicked , system will prompt with Record Macro dialog box as seen below.

Here the available fields can be changed by user. Macro Name can be any combination of key strokes of your choice. Keep the name you can relate it for job you want your macro to do. Once you click OK button, system is ready to start recording you actions and following toolbar appears on your screen to stop macro recording

Now you perform the task you want you macro to repeat,
Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make are also recorded. When you record macros, Visual Basic stores each macro in a new module attached to a workbook.

Once you are through with all the steps , you need to stop recording by clicking on . Now your macro has been created by Excel using a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros.

Automating tasks in Excel : Using Macros in excel

If you are doing a task again a again in excel you can automate it with a Marco. If you receive a data every day/Week/Month that you analyse the same way every time, you can create series of commands in shape of excel macro and do that task by single command of running that macro and completing your task by single click of mouse.

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Macros are easy to create: you tell Excel to start recording, perform actions as you normally do, and then tell Excel when you're done. Behind the scenes, Excel uses a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros that will save you time and make your work easier

Refer to Recording Excel Macro / Writing Excel Macro for further details on Macros in Excel