How to create report from Excel data sheet with VBA


Here is a message from one of our subscribers:

‘My name is Igor Izaguirre and I live in México City. I have a doubt regarding an Excel Macro (using VBA of course). My wife is a Wedding Planner and I want to help her creating a Macro in order to make Budgets for her clients.

1. I have many worksheets.
2. Each worksheet is named for example: the name of every DJ (different worksheets for each DJ) and its cells includes for example A1= Package 1: $1,000, A2= Package 2: $2,000, A3= Package 3: $3,000.
3. Other worksheets include the names of the Florists. And the cells includes: A1= Basic: $5,000. A2= Medium: $13,000. A3= Premium: $20,000
4. Other worksheets include the names of Caterers. The cells includes: A1= 100 guests: $30,000 A2= 200 guests: $50,000 A3= 300 guests: $80,000

How can I create a Macro writing the name of the DJ, or Florit or Caterer and its package in a specific Cell. Run a Macro so that the macro could search for the name of the DJ, Florit or Caterer etc… among all the worksheets, locate the package I wrote and copy/paste the information I need in a new Worksheet?

Thanks a lot for your help!

Kind regards,

Igor

We have simplified the complete procedure for Igor for creating a report from Excel data. We enter all the data of the suppliers or service providers with their different services and prices in an Excel worksheet and name the sheet as ‘Data’. Next we create 3 command buttons to extract the data from the Data worksheet into the Report worksheet with VBA step by step. The first command button (Get Dj) extracts the data of the different DJs. The second command button captioned ‘Get Florist’ gets us the required florists data and the ‘Get Caterer’ command button gets us the caterer services details. We have also created a commend button called ‘View Report’ to view our report in one click. Finally if don’t need the report we can clear the Report sheet data quickly and easily using the ‘Clear Report’ command button.

We can add more headers to our data sheet to make our report even more comprehensive. We can add a location header. Let’s say our business stretches over 4 districts in our city and we have identified service providers in all the 4 districts, then we can extract data of a specific location depending on on our customer’s preferences or other commercial considerations.

We have solved this wedding planner problem with VBA but it should be obvious that the solution can be used by financial planners, insurance agents, realty agents, etc.

Watch the video below to see how we can create a report from the Excel data sheet quickly and easily before you study the VBA code of all the command buttons:

 


Watch the same video on YouTube

Here is the complete VBA code for the project:

Private Sub CommandButton1_Click()
Dim dj As String

‘Let’s start at row 2. Row 1 has headers
x = 2

Sheets(“Data”).Select
dj = InputBox(“Enter DJ Package. Choices are Package1, Package2 or Package3.”)

‘Start the loop
Do While Cells(x, 1) <> “”
‘Look for data of dj
If Cells(x, 3) = dj Then
Worksheets(“Data”).Rows(x).Copy
Worksheets(“Report”).Activate
erow = Sheets(“Report”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Report”).Rows(erow)
End If
Worksheets(“Data”).Activate
x = x + 1
Loop
Application.CutCopyMode = False

End Sub

Private Sub CommandButton2_Click()
Dim florist As String
Sheets(“Data”).Select
x = 2
Sheets(“Data”).Select
florist = InputBox(“Enter Florist Service. Choices are Basic, Medium or Premium.”)
Do While Cells(x, 1) <> “”
‘Look for data of florist
If Cells(x, 3) = florist Then
Worksheets(“Data”).Rows(x).Copy
Worksheets(“Report”).Activate
erow = Sheets(“Report”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Report”).Rows(erow)
End If
Worksheets(“Data”).Activate
x = x + 1
Loop
Application.CutCopyMode = False

End Sub

Private Sub CommandButton3_Click()
Dim caterer As String
Sheets(“Data”).Select
x = 2
Sheets(“Data”).Select
caterer = InputBox(“Enter Caterer Type. Choices are 100 Guests, 200 Guests, 300 Guests.”)
Do While Cells(x, 1) <> “”
‘Look for data of caterer
If Cells(x, 3) = caterer Then
Worksheets(“Data”).Rows(x).Copy
Worksheets(“Report”).Activate
erow = Sheets(“Report”).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Report”).Rows(erow)
End If
Worksheets(“Data”).Activate
x = x + 1
Loop
Application.CutCopyMode = False
End Sub

Private Sub CommandButton4_Click()

Sheets(“Report”).Select
x = 2
Do While Cells(x, 1) <> “”
Sheets(“Report”).Range(Cells(x, 1).Address, Cells(x, 4).Address).ClearContents
x = x + 1
Loop

End Sub

Private Sub CommandButton5_Click()
Sheets(“Report”).Select
End Sub

Download sample file by clicking on the Excel icon:

7 thoughts on “How to create report from Excel data sheet with VBA

  1. Raghavi

    Hi,

    I am very new to VBA.I have one Excel sheet with some data in it.I want code to open that Excel sheet in HTML Web Page using VBA.Please help me with the solution.

    Reply
  2. Sharaf

    i need to have solution for below concept.

    I Have worksheet named invest data
    1. Sheet1 having data example Coulum “A” name, “B,C,D,…….” Months (Jan-Dec) at the end “K” Total
    A2= Lois, B2=150, C2=300, D2=150…. and K is Total of row
    2. Sheet 2 having values related to Sheet1
    example Sheet 2
    A2=Lois, B2=40, C2=80, …….and likes K is Total of row

    I need to view report in another sheet like as follows;
    Place a ComboBox with range of Sheet1 A

    when i selecet any name in comboBox list, then click on comman button should copy the following cells to new sheet called report corresponing to appropriate name in cooum A1
    lets call i select Lois then
    Sheet1 A2,
    Sheet1B2
    Sheet2 K2 etc,

    can you please explain me how to code for above in excell.

    Reply
  3. Pingback: How to create report from Excel data sheet with VBA | Coding Tweaks

Leave a Reply

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