Create report with headers at specific positions using Excel VBA


How to create a report with headers at specific positions using Excel VBA. This is a reply to the following query:

Dr. Dinesh,

Hello. My name is Kiyoshi working for a bank. I have used several VBA coding examples you have put up on the web and these have been very helpful. I am however having one difficulty to solve the issue below.

I have a table with Team Name in one column and System Name in another column (Original Table below). In each row, one system is mapped to a team and because each team is using several different systems, systems used by each team is separated into multiple rows. I would like to know how I can merge the rows by team names with all systems in one row but in different lines (like New Table below). The new table should be created to a new worksheet.

Thank you very much for your help in advance.

Original Table:

Original Table with Headers
Original Table with Headers

New Table:

New Table with Headers in specific positions
New Table with Headers in specific positions

Watch the training video below before you work with the VBA code:


Watch this video on YouTube.

Sub createReport()
Dim lastrow As Long, erow As Long
Dim countfinance As Long, counthuman As Long, countsales As Long

countfinance = 0
counthuman = 0
countsales = 0

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Sheet2.Cells.Clear

Sheet2.Range(“a1”) = “Team Name”
Sheet2.Range(“a1”).Font.Bold = True
Sheet2.Range(“b1”) = “System”
Sheet2.Range(“b1”).Font.Bold = True

For i = 2 To lastrow
If Sheet1.Cells(i, 1) = “Finance” Then
countfinance = countfinance + 1
Sheet1.Cells(i, 2).Copy
erow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)

ElseIf Sheet1.Cells(i, 1) = “Human Resources” Then
counthuman = counthuman + 1
Sheet1.Cells(i, 2).Copy
erow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)

ElseIf Sheet1.Cells(i, 1) = “Sales” Then
countsales = countsales + 1
Sheet1.Cells(i, 2).Copy
erow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)
End If

Next i

Application.CutCopyMode = False
Sheet2.Cells(2, 1) = “Finance”
Sheet2.Cells(countfinance + 2, 1) = “Human Resources”
Sheet2.Cells((countfinance + 2 + counthuman), 1) = “Sales”
Sheet2.Columns.AutoFit
Range(“a1”).Select

End Sub

If we wish to sort the data in sheet1 in ascending order we can use the following recorded macro code:
Sub Macro1()

‘ Macro1 Macro


Sheet1.Range(“A2:B18”).Select
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“A2:A18”) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A1:B18”)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Download a sample Excel file:


Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Create report with headers at specific positions using Excel VBA”

  1. Hello Sir…

    im trying to do something that will generate how many males and females in the data i inputed. any tips on how to do it?? im trying everythin but nothing works.

    hope you can help me…thank you

Leave a Reply

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