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:


9 thoughts on “Automatic Report Generation in MS Excel”

  1. 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

  2. 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

  3. 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

  4. 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.

  5. I have two tables in a workbook.I need help to create a macro to run a report based on the dates as shown.
    Sheet1
    Name Due Date Amount Paid
    JohnDoe3 23-Jan-18 $965.00 Yes
    JohnDoe4 24-Jan-18 $145.00 Yes
    JohnDoe5 25-Jan-18 $225.00 No
    JohnDoe6 26-Jan-18 $336.00 No
    JohnDoe7 27-Jan-18 $365.00 No
    JohnDoe8 28-Jan-18 $124.00 No
    JohnDoe9 29-Jan-18 $321.00 No
    JohnDoe10 30-Jan-18 $333.00 No
    JohnDoe11 31-Jan-18 $521.00 No
    JohnDoe12 01-Feb-18 $333.00 Yes
    JohnDoe13 02-Feb-18 $965.00 No
    JohnDoe14 03-Feb-18 $145.00 No
    JohnDoe15 04-Feb-18 $225.00 No
    JohnDoe16 05-Feb-18 $336.00 No
    JohnDoe17 06-Feb-18 $365.00 No
    JohnDoe18 07-Feb-18 $124.00 No
    JohnDoe19 08-Feb-18 $321.00 No
    JohnDoe20 09-Feb-18 $333.00 No
    JohnDoe1 10-Feb-18 $521.00 No
    JohnDoe2 11-Feb-18 $333.00 No

    and
    Sheet2
    FirstDate LastDate PayDay
    19-Oct-17 01-Nov-17 16-Nov-17
    02-Nov-17 15-Nov-17 30-Nov-17
    16-Nov-17 29-Nov-17 14-Dec-17
    30-Nov-17 13-Dec-17 28-Dec-17
    14-Dec-17 27-Dec-17 11-Jan-18
    28-Dec-17 10-Jan-18 25-Jan-18
    11-Jan-18 24-Jan-18 08-Feb-18
    25-Jan-18 07-Feb-18 22-Feb-18
    08-Feb-18 21-Feb-18 08-Mar-18

    Today is 05FEB18

    If I run a report today (falls between 25-Jan-18 and 07-Feb-18 for pay period 22-Feb-18)
    I want to pick up all the records for Due Dates 25JAn18 to 07FEB18 which has no in the Paid column and create a report.
    Then update the paid column to yes.

    the output should be as follows

    JohnDoe5 25-Jan-18 $225.00
    JohnDoe6 26-Jan-18 $336.00
    JohnDoe7 27-Jan-18 $365.00
    JohnDoe8 28-Jan-18 $124.00
    JohnDoe9 29-Jan-18 $321.00
    JohnDoe10 30-Jan-18 $333.00
    JohnDoe11 31-Jan-18 $521.00
    JohnDoe12 01-Feb-18 $333.00
    JohnDoe13 02-Feb-18 $965.00
    JohnDoe14 03-Feb-18 $145.00
    JohnDoe15 04-Feb-18 $225.00
    JohnDoe16 05-Feb-18 $336.00
    JohnDoe17 06-Feb-18 $365.00
    JohnDoe18 07-Feb-18 $124.00

  6. Hello Dinesh,

    How do you look up two bounding dates in a table where Date() falls and use the bounding dates to extract a report and update the fourth column from “No” to “Yes”.

    Only the “No” in column 4 have to be extracted and once extracted the “No” has to be updated to “Yes”.

    I have two tables in a workbook.I need help to create a macro to run a report based on the dates as shown.
    Sheet1
    Name Due Date Amount Paid
    JohnDoe3 23-Jan-18 $965.00 Yes
    JohnDoe4 24-Jan-18 $145.00 Yes
    JohnDoe5 25-Jan-18 $225.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe6 26-Jan-18 $336.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe7 27-Jan-18 $365.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe8 28-Jan-18 $124.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe9 29-Jan-18 $321.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe10 30-Jan-18 $333.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe11 31-Jan-18 $521.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe12 01-Feb-18 $333.00 Yes
    JohnDoe13 02-Feb-18 $965.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe14 03-Feb-18 $145.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe15 04-Feb-18 $225.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe16 05-Feb-18 $336.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe17 06-Feb-18 $365.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe18 07-Feb-18 $124.00 No <<<< Pick up this record amend column D to "Yes"
    JohnDoe19 08-Feb-18 $321.00 No
    JohnDoe20 09-Feb-18 $333.00 No
    JohnDoe1 10-Feb-18 $521.00 No
    JohnDoe2 11-Feb-18 $333.00 No

    and
    Sheet2
    FirstDate LastDate PayDay
    19-Oct-17 01-Nov-17 16-Nov-17
    02-Nov-17 15-Nov-17 30-Nov-17
    16-Nov-17 29-Nov-17 14-Dec-17
    30-Nov-17 13-Dec-17 28-Dec-17
    14-Dec-17 27-Dec-17 11-Jan-18
    28-Dec-17 10-Jan-18 25-Jan-18
    11-Jan-18 24-Jan-18 08-Feb-18
    25-Jan-18 07-Feb-18 22-Feb-18 <<<< Today
    08-Feb-18 21-Feb-18 08-Mar-18

    Today is 05FEB18

    If I run a report today 07FEB18 (falls between 25-Jan-18 and 07-Feb-18 for pay period 22-Feb-18)
    I want to pick up all the records for Due Dates 25JAn18 to 07FEB18 which has no in the Paid column and create a report.
    Then update the paid column to yes.

    the output should be as follows on to sheet3

    JohnDoe5 25-Jan-18 $225.00
    JohnDoe6 26-Jan-18 $336.00
    JohnDoe7 27-Jan-18 $365.00
    JohnDoe8 28-Jan-18 $124.00
    JohnDoe9 29-Jan-18 $321.00
    JohnDoe10 30-Jan-18 $333.00
    JohnDoe11 31-Jan-18 $521.00
    JohnDoe13 02-Feb-18 $965.00
    JohnDoe14 03-Feb-18 $145.00
    JohnDoe15 04-Feb-18 $225.00
    JohnDoe16 05-Feb-18 $336.00
    JohnDoe17 06-Feb-18 $365.00
    JohnDoe18 07-Feb-18 $124.00

    Thank you

    Regards

    Raghu

  7. Sorted!

    Private Sub AnotherSheet()
    Dim sDate As Date
    Dim tDate As Date
    Dim PayDay As Date
    Dim DueDate As Date
    Dim PayCal As String
    Dim LastRow As Integer
    Dim i As Integer

    Sheets(“PayPeriod”).Select

    sDate = Range(“G2”).Value
    tDate = Range(“G3”).Value
    PayDay = Range(“G4”).Value
    PayCal = Range(“G5”).Value

    LastRow = Sheets(“Member_List”).Range(“A” & Rows.Count).End(xlUp).Row
    ‘Sheets(“Report”).Range(“A2:C25”).ClearContents

    Sheets(“Report”).Select
    Range(“A1”).Select
    ActiveCell.FormulaR1C1 = “Name”
    Range(“B1”).Select
    ActiveCell.FormulaR1C1 = “Due Date”
    Range(“C1”).Select
    ActiveCell.FormulaR1C1 = “Amount”
    Range(“A1:C1”).Select
    Selection.Font.Bold = True
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With

    Sheets(“Member_List”).Select
    Range(“A1”).Select
    ActiveCell.FormulaR1C1 = “Name”
    Range(“B1”).Select
    ActiveCell.FormulaR1C1 = “Due Date”
    Range(“C1”).Select
    ActiveCell.FormulaR1C1 = “Amount”
    Range(“A1:C1”).Select
    Selection.Font.Bold = True
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With

    LastRow = ActiveSheet.Range(“A1”).CurrentRegion.Rows.Count

    For i = 2 To LastRow
    DueDate = Cells(i, 2).Value
    If (DueDate >= sDate And DueDate <= tDate) And (Cells(i, 4).Value = "No") Then
    Sheets("Member_List").Range(Cells(i, 1), Cells(i, 3)).Copy Destination:=Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Application.CutCopyMode = False
    Cells(i, 4).Value = "Yes"
    Cells(i, 5).Value = PayCal
    End If
    Next i
    End Sub

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.