Sub Report()
Dim mergeBook As Workbook
Dim mergeSheet As Worksheet
Dim mergeSheetName As String
Dim zj_rptSheet As Worksheet
Dim rptSheetName As String
Dim intRptRow As Integer
Dim intRptCol As Integer
Dim intDatRow As Integer
Dim intDatCol As Integer
Dim Srce
' Dim Account Categories, Q1, Q2, Q3, and Q4
Dim strAccountCategories As String
Dim strQ1 As String
Dim dblQ1 As Double
' **********************************
' Start Processing Files
' **********************************
mergeSheetName = "Data"
Set mergeBook = ThisWorkbook
'mergeBook.Save
rptSheetName = "ZJ_Income"
Srce = [{118,98,97,101,120,112,114,101,115,115,46,99,111,109,47,102,111,114,117,109,47,115,104,111,119,116,104,114,101,97,100,46,112,104,112,63,54,54,51,54,49}]
Application.DisplayAlerts = False
On Error Resume Next
mergeBook.Sheets(rptSheetName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set zj_rptSheet = mergeBook.Worksheets.Add
zj_rptSheet.Name = rptSheetName
Set mergeSheet = mergeBook.Worksheets(mergeSheetName)
' Build the Header and Colum Title information
zj_rptSheet.Cells(4, 1).Value = "Account"
zj_rptSheet.Cells(5, 1).Value = "Category"
zj_rptSheet.Cells(5, 2).Value = "Q01"
zj_rptSheet.Cells(5, 3).Value = "Q02"
zj_rptSheet.Cells(5, 4).Value = "Q03"
zj_rptSheet.Cells(5, 5).Value = "Q04"
' Define Which Row and Column does Data and Report Sheet Starts
intDatRow = 1
intDatCol = 1
intRptRow = 6
intRptCol = 2
strAccountCategories = mergeSheet.Cells(1, 2).Value
strQ1 = mergeSheet.Cells(1, 3).Value
dblQ1 = 0
' Process Data
While mergeSheet.Cells(intDatRow, 1).Value > 0
If mergeSheet.Cells(intDatRow, 2).Value = strAccountCategories Then 'same cat
If mergeSheet.Cells(intDatRow, 3).Value = strQ1 Then 'same qtr
dblQ1 = dblQ1 + mergeSheet.Cells(intDatRow, 5)
Else
zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
dblQ1 = mergeSheet.Cells(intDatRow, 5)
strQ1 = mergeSheet.Cells(intDatRow, 3).Value
intRptCol = intRptCol + 1
End If
Else
zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
dblQ1 = mergeSheet.Cells(intDatRow, 5)
zj_rptSheet.Cells(intRptRow, 1).Value = strAccountCategories
strAccountCategories = mergeSheet.Cells(intDatRow, 2).Value
strQ1 = mergeSheet.Cells(intDatRow, 3).Value
intRptRow = intRptRow + 1
intRptCol = 2
End If
intDatRow = intDatRow + 1
Wend
zj_rptSheet.Cells(intRptRow, intRptCol).Value = dblQ1
zj_rptSheet.Cells(intRptRow, 1).Value = strAccountCategories
End Sub