Automatic Report Generation in MS Excel

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, Appointment 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:


6 thoughts on “Automatic Report Generation in MS Excel

  1. Isaimani Bala

    Dear Sir
    Thanks for sharing the above codes.
    I have a question.
    How can I copy the data to the different work sheet from the above example. Also I would like to select two criteria to match before the data are copied from the table.
    I will be glad to watch the video.

    Thanks in advance
    Isaimani Bala

    Reply
  2. shrishail c k

    Hello Sir,
    I make a project in student admission system. I save students details in sheet 1, fees details in sheet 2, marks entry in sheet3. here I have problem that data can be retrieved from sheet1, and update the same, how can I access data from another 2 sheets in user form to find and update the data……

    thanking you

    Reply
  3. Marion Walker

    Hello, Firstly thank you for your great videos and instructions. I’ve used your code to transfer data from the master workbook to separate workbooks based on criteria i.e. today’s date and Trial name e.g. APPLES. I have copied the code and changed it as necessary for each separate workbook. However, if data is added throughout the day, I can only run this macro at the very end of the day but I need to be able to work on the separate workbooks during the day. What I want is to enhance the macro so that after adding data to the master and running the macro to copy to individual workbooks, today’s date then changes to “Transferred”. This will show what has already been transferred and also avoid sending duplications to the separate workbooks. Is this possible? The code is below. Many thanks in advance. Marion

    Sub mytrials()

    Dim LastRow As Integer, i As Integer, erow As Integer

    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

    If Cells(i, 1) = Date And Cells(i, 2) = “APPLES” Then
    Range(Cells(i, 1), Cells(i, 6)).Select
    Selection.Copy

    Workbooks.Open Filename:=”C:\Users\Marion\Documents\AllTrialsMaster\APPLES.xlsx”
    Worksheets(“Sheet1”).Select
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False
    End If

    Next i

    Reply
  4. Erica

    Dear Sir/Madam,

    I have just conducted a survey with 800 schools. I have all their data in an excel spreadsheet. However, in terms of analysing and dissemination of results – I have to give each school individual comparative feedback. Therefore each school will get their own report with unique data different to another school.

    If I have all the results stored for example on an excel file (this includes numeric, tables, graphs) is there excel able to take this information and then populate an existing report template on MS Word? Basically I am looking for a software that works kind of like mail merge – so I do not have to make 800 individual feedback scorecards manually.

    I hope I have explained this well.

    Your help and guidance will be much appreciated.

    Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *