GENERATE REPORT - USING VBA

Question

GENERATE REPORT - USING VBA

Anonymous

Hi,

   Data sheet have some Basic Data there,  how to generate meaningful  report  using Macro or advance formula like  output sheet. **Total pending Means purchase less sales.**

Below Example Website referred , i am More struggling

https://www.thesmallman.com/subtotal-on-the-fly#:~:text=Sub%20aSubTotal()%20'Excel%20VBA%20to%20create%20subtotals.&text='Sort%20the%20data%20so%20like%20data%20is%20grouped%20together.&text=The%20following%20will%20remove%20the%20subtotals%20you%20just%20added.&text=The%20Excel%20file%20contains%20the,the%20example%20dataset%20shown%20above.

Refer link Below link , i uploaded my sheet in one Drive

sample data_13 07 2023.xlsx

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

8 additional answers

Answer 1

Hi there

EDITED:

Here is the link to your sample file with the VBA answer to your question

https://we.tl/t-XtvDamu7zl

Heare are the macros

Sub AddSubTotals()

'Excel VBA to create subtotals.

''' by Jeovany CV @MS.Excel Forum_15-Jul-2023

Dim myData As Range

Dim i As Long

Dim voucher As String

Dim ton As Double

Dim subTotal As Double

Application.ScreenUpdating = False

Set myData = Range("A3", Range("H" & Rows.Count).End(xlUp)) ''' Data values only, Headers are excluded

myData.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo ''' Sort your data

i = 3

Do While Range("A" & i) <> ""

            voucher = Range("C" & i).Value 

            ton = Range("E" & i).Value 

            If voucher = "SALES" Then ton = -ton 

            subTotal = subTotal + ton 

            If Range("A" & i) &lt;&gt; Range("A" & i + 1) Then 

                    Rows(i + 1 & ":" & i + 2).EntireRow.Insert Shift:=xlDown 

                    Range("D" & i + 1).Value = "TOTAL PENDING" 

                    Range("E" & i + 1).Value = subTotal 

                    Range("D" & i + 1).Resize(1, 2).Font.Bold = True 

                    i = i + 2 

                    subTotal = 0 

            End If 

            i = i + 1

Loop

Application.ScreenUpdating = True

End Sub

Sub Restore()

Dim bookNums As Range

Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row 

    Set bookNums = Range("A3", Range("A" & lastRow + 1)) 

    bookNums.SpecialCells(4).EntireRow.Delete

End Sub

Regards

Jeovany

Answer 2

Anonymous

https://thedatalabs.org/definitive-guide-to-ado-in-excel-and-vba/

0 comments

Answer 3

use sql?

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/using-ado-with-microsoft-visual-basic#handling-events

create temp table aa as

select BOOKING NO ord,* from GENERATE_REPORT_subtotal union all

select BOOKING NO ord,'' BOOKING NO,''

DATE,'' VOUCHER,'TOTAL PENDING'

as PARTY NAME,sum(TON) TON,'' BASE VALUE,'' NOTE,'' NARATION from GENERATE_REPORT_subtotal group by BOOKING NO;

select * from aa order by ord;

Answer 4

Anonymous

Thanks for reply. But I need VBA solutions, power pivot table with data model connection is possible.

0 comments

Answer 5

No vba in this one, just make sure that all your data is within the table on your data sheet, then refresh the pivot at cell J3 of your output sheet.

The order in which the columns appear are a bit different.

See linked file: https://app.box.com/s/02jh1zndf5qmk39zbafiflevrwrdbr7y

Share via

GENERATE REPORT - USING VBA

8 additional answers