Automatic Report Generation in MS-Excel

Home More Videos

A MS-Excel  user's query:
I'm working as a personal assistant to the General Manager in a large company. I fix appointments with decision makers in different companies to make a presentation of our products and services. Every evening I have to produce a quick report in MS-Excel from my data that is attached. Is it possible to copy specific data and make a quick report by selecting let's say just the header the 'Name of the decision maker' and the data below it in column A? Thanks for your help.
How to generate quick reports in MS-Excel:
  • Our data is in Sheet1 with the headers Name of decision maker, Company Turnover, Type of Business, City, Mobile, Apointment Fixed and Date
  • Now we wish to display the data Name of decision maker, Appointment Fixed and Date as a report in another area of the worksheet automatically if the data under the header Name of decsion maker changes i.e. if we add or delete a name
  • In Excel 2007 Click on the Developer tab
  • Select Visual Basic from the code tab
  • In the Visual Basic for Applications window double-click on Sheet1
  • Next to  the General text box click on the drop-down menu and select Worksheet
  • Automatically you'll see SelectionChange in the text box on the right and the two lines of code appear as shown below
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    End Sub
  • Between these two lines of code we'll write our code
  • In Excel 2003 which you are seeing in the video below you'll have to click on 'Tools' in the menu bar and then select Visual Basic editor. Of course you can press the Alt+F11 to come quickly to the VBA editor
  • The next steps are the same as in Excel 2007 or 2010
  • You can reach the VBA editor also by right-clicking on the Sheet1 and selecting 'View Code'
The complete VBA code is reproduced below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Something will happen if you change the data in column 1
If Target.Column = 1 Then
'Copy the header in the same row but in the 10th column i. e. column J
Target.Offset(0, 0).Copy Destination:=Target.Offset(0, 10)
'Copy the mobile phone number in the same row but to column K
Target.Offset(0, 5).Copy Destination:=Target.Offset(0, 11)
'Copy the Appointment Fixed data to column L in the same row
Target.Offset(0, 6).Copy Destination:=Target.Offset(0, 12)
End If

End Sub

We can run a VBA code or macro automatically on a worksheet when a specific data range defined by a parameter called 'Target' is changed in the 'Worksheet_SelectionChange Event'.

Watch the video below to learn how to automatically generate reports: