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”

Isaimani Bala says:

July 19, 2014 at 4:10 pm

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
1. Dinesh Kumar Takyar says:
  
  August 22, 2014 at 1:53 pm
  
  These two links might help:
  http://www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
  
  http://www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/
  
  Also, please run a search to find more solutions on our website.
  
  Reply
shrishail c k says:

September 12, 2014 at 3:07 pm

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
Marion Walker says:

February 16, 2015 at 5:30 pm

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
Erica says:

November 4, 2016 at 1:48 am

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
1. Dinesh Kumar Takyar says:
  
  November 5, 2016 at 5:30 am
  
  This link might help: http://www.exceltrainingvideos.com/tag/automate-mail-merge-without-word/
  
  Reply
Raghu Prabhu says:

February 5, 2018 at 12:28 am

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

Reply
Raghu Prabhu says:

February 7, 2018 at 8:25 am

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

Reply
Raghu Prabhu says:

February 16, 2018 at 9:43 pm

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

Reply

9 thoughts on “Automatic Report Generation in MS Excel”

Leave a Reply Cancel reply