How to automatically generate report by VBA?

[Andy23]

Hello Everyone,

I would like to make an automatically report by VBA.
I have 2 sheets which have two tables, Table 1 is the needed result after running VBA and Table 2 is the details of the data report.
Pls note that in table 1 is fully dynamic without key in any manual data, and the total receipts will be counted uniquely also.
Thank you!

 

[Marc L]

Hello,​

​

as VBA can't work with Google sheets !​

If you want to use Excel so attach here at least an Excel workbook saved as binary format .xlsb​

with a better elaboration as 'fully dynamic' has not such sense …​

​

 

[Andy23]

Hello Sir,

Yes, I upload my excel in google sheet for the expert to download. I finally find where to upload the file.
Thank you

 

[Marc L]

As that is useless just uploading directly your workbook within this forum …​

​

 

[Andy23]

Hello Sir,

Now I am attached the file.
my apologies as I am a new member of this forum.
Thank you!

 

[Marc L]

Which Excel version do you have ?​

If 365 so you do not ever need any VBA procedure as all can be done directly with worksheet formulas …​

Or maybe with Power Query (Get & Transform) …​

​

 

[p45cal]

Two vba-free solutions in the attached:

• At cell M2 of the Summary sheet, a pivot table (via Data Model to allow distinct count)
• At cell R2 of the Summary sheet, a Power Query solution.

All that's needed is for the source table to be updated by you, then the other two tables can be refreshed (right-click and choose Refresh.

 

[vletm]

... or study Yourself ...
Google excel count of unique values pivot

 

[Marc L]

the other two tables can be refreshed (right-click and choose Refresh.

Hi, I just wanna know if that takes time as under VBA the calculation is instant, thanks …​

​

 

[Andy23]

Two vba-free solutions in the attached:

• At cell M2 of the Summary sheet, a pivot table (via Data Model to allow distinct count)
• At cell R2 of the Summary sheet, a Power Query solution.

All that's needed is for the source table to be updated by you, then the other two tables can be refreshed (right-click and choose Refresh.

Hello Sir,

Thank you!
I also did it by Pivot table and distinct count also. And thank for the Power Query.
I want to know if there is any VBA code that can run the report like that.
Thank you!

 

[Andy23]

Which Excel version do you have ?​

If 365 so you do not ever need any VBA procedure as all can be done directly with worksheet formulas …​

Or maybe with Power Query (Get & Transform) …​

​

Hello Sir,
I have the solution for 365 but the data loading is very slow. So I am trying with VBA if it may fast than excel formula.

Thank you!

 

[p45cal]

but the data loading is very slow

What do you mean by this?
Getting the raw data into Table B is slow?
Or that the formulae you're using to produce the result table are very slow?

Why don't you attach your formula solution here and let us see if it can be streamlined?
Does your version of 365 allow the use of formulae including:
TOCOL
FILTER
SEQUENCE
BYROW
LAMBDA
LET
?

 

[Marc L]

So I am trying with VBA if it may fast than excel formula.

According to your attachment a last century VBA demonstration for starters :​

Sub Demo1()
    Dim V, W(), R&, L&, oCol As Collection
        V = Sheet2.ListObjects(1).Range
        ReDim W(1 To UBound(V), 1 To 4)
        On Error Resume Next
    For R = 2 To UBound(V)
     If V(R, 1) <> V(R - 1, 1) Then
        W(L, 3) = oCol.Count
        L = L + 1
        W(L, 1) = V(R, 1)
        Set oCol = New Collection
     End If
        oCol.Add Empty, CStr(V(R, 3))
        W(L, 2) = W(L, 2) + V(R, 11)
        W(L, 4) = W(L, 4) + V(R, 9)
    Next
        On Error GoTo 0
        W(L, 3) = oCol.Count
    With Sheet1.ListObjects(1)
     If .ListRows.Count < L Then
        .Resize .Range.Resize(L + 1)
        .DataBodyRange.Columns(2).NumberFormatLocal = .Range(2, 2).NumberFormatLocal
     ElseIf .ListRows.Count > L Then
        .Range.Rows(L + 2 & ":" & .Range.Rows.Count).Delete
     End If
        .DataBodyRange = W
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vletm]

Andy23​

Other sample with VBA...
Did You write somewhere - how long time this takes Your way?
Usage: With this sample, You need to select Summary-sheet to get results ...
and You'll save time few seconds - no need to find or run any code?
There is also sorting, which could take away (sorting takes time too), if You would like to use Your own time for that (sorting).
This also deletes previous results before new results will show. Many this kind of minor tasks take time anyway.

 

[Marc L]

For sure time saved, instant result with my post #13 demonstration (needs less than 0.08 second on a ten years old tests laptop) …​

​

 

[Andy23]

According to your attachment a last century VBA demonstration for starters :​

Sub Demo1()
    Dim V, W(), R&, L&, oCol As Collection
        V = Sheet2.ListObjects(1).Range
        ReDim W(1 To UBound(V), 1 To 4)
        On Error Resume Next
    For R = 2 To UBound(V)
     If V(R, 1) <> V(R - 1, 1) Then
        If L Then W(L, 3) = oCol.Count
        L = L + 1
        W(L, 1) = V(R, 1)
        Set oCol = New Collection
     End If
        oCol.Add Empty, CStr(V(R, 3))
        W(L, 2) = W(L, 2) + V(R, 11)
        W(L, 4) = W(L, 4) + V(R, 9)
    Next
        On Error GoTo 0
        W(L, 3) = oCol.Count
    With Sheet1.ListObjects(1)
     If .ListRows.Count < L Then
        .Resize .Range.Resize(L + 1)
        .DataBodyRange.Columns(2).NumberFormatLocal = .Range(2, 2).NumberFormatLocal
     ElseIf .ListRows.Count > L Then
        .Range.Rows(L + 2 & ":" & .Range.Rows.Count).Delete
     End If
        .DataBodyRange = W
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Hello Sir,

It works perfectly. So impressive as it takes only few seconds to generate the report.
However, I got 2 errors if the date is not in sequence, please check excel file "Report VBA - 03". And another error if I add more column to the right sides with the formula, then it will return "#NA", pls check VBA - 02. If possible could u pls make the VBA to be active without running it manually? Thank you Sir!

 

[Andy23]

Andy23​

Other sample with VBA...
Did You write somewhere - how long time this takes Your way?
Usage: With this sample, You need to select Summary-sheet to get results ...
and You'll save time few seconds - no need to find or run any code?
There is also sorting, which could take away (sorting takes time too), if You would like to use Your own time for that (sorting).
This also deletes previous results before new results will show. Many this kind of minor tasks take time anyway.

Hello Sir,

I already tried you VBA and It works perfectly as taking a bit time to generate report. And yes the data also sort the date as u mentioned.
May I confirm this VBA will be only using the same excel file that I attached because when I copy to my working file, then its not work.
Thank you!

 

[Andy23]

What do you mean by this?
Getting the raw data into Table B is slow?
Or that the formulae you're using to produce the result table are very slow?

Why don't you attach your formula solution here and let us see if it can be streamlined?
Does your version of 365 allow the use of formulae including:
TOCOL
FILTER
SEQUENCE
BYROW
LAMBDA
LET
?

Hello Sir,
I used LET and Filter...
However, the expert here already shared the VBA which take only few seconds to run the large data report.

Thank you!

 

[vletm]

Andy23​

I already tried you VBA and It works perfectly as taking a bit time to generate report.
Of course it takes time.
And yes the data also sort the date as u mentioned.
... and deleted previous results.
May I confirm this VBA will be only using the same excel file that I attached because when I copy to my working file, then its not work.
It should work with 'any Excel-file'. Especially, if layout is same as Your original ... was.
Check below snapshot - You should notice two major differences.

 

[Marc L]

However, I got 2 errors if the date is not in sequence

As I wrote « according to your attachment » so yes the VBA procedure assumes the dates are already sorted …​

The reason why I share VBA procedures for starters in order people can fit them​

for what they misexplained / forgot or when they change the layout, easy to start with the Macro Recorder.​

Proceeding like this means those people are enough confident with their Excel / VBA skills to fit themselves​

any VBA procedure helpers can share.​

If it is not the case, this is a non sense to create such thread, the reason why whatever the Excel forum​

the initial elaboration must be accurate and the workbook attachment must reflect exactly the original data and layout …​

​

For the N/A error just add the 4 columns reference to the last DataBodyRange codeline.​

​

No idea what could mean your « VBA to be active without running it manually » as you can associate the procedure to a button​

or include it in a worksheet event like vletm demonstrated to you within its post #14 attachment​

and where he included the code to sort on dates so you already have all the necessary.​

​

For the non VBA people the obvious way is the Excel features like worksheet formulas, pivot, …​

​

 

[Andy23]

Andy23​

I already tried you VBA and It works perfectly as taking a bit time to generate report.
Of course it takes time.
And yes the data also sort the date as u mentioned.
... and deleted previous results.
May I confirm this VBA will be only using the same excel file that I attached because when I copy to my working file, then its not work.
It should work with 'any Excel-file'. Especially, if layout is same as Your original ... was.
Check below snapshot - You should notice two major differences.
View attachment 84975

Hello Sir,

Thank you for your explanation.
I have follow ur instruction and try in another excel and change the sheet/table name. Thank it works perfectly.
Thank you so much!

 

[Andy23]

As I wrote « according to your attachment » so yes the VBA procedure assumes the dates are already sorted …​

The reason why I share VBA procedures for starters in order people can fit them​

for what they misexplained / forgot or when they change the layout, easy to start with the Macro Recorder.​

Proceeding like this means those people are enough confident with their Excel / VBA skills to fit themselves​

any VBA procedure helpers can share.​

If it is not the case, this is a non sense to create such thread, the reason why whatever the Excel forum​

the initial elaboration must be accurate and the workbook attachment must reflect exactly the original data and layout …​

​

For the N/A error just add the 4 columns reference to the last DataBodyRange codeline.​

​

No idea what could mean your « VBA to be active without running it manually » as you can associate the procedure to a button​

or include it in a worksheet event like vletm demonstrated to you within its post #14 attachment​

and where he included the code to sort on dates so you already have all the necessary.​

​

For the non VBA people the obvious way is the Excel features like worksheet formulas, pivot, …​

​

Hello Sir,

Thank you so much for your explanation.
For the N/A error just add the 4 columns reference to the last DataBodyRange codeline.
I tried to change the edit, but it's still not work.
Have a great day Sir!

 

[Marc L]

I tried to change the edit, but it's still not work

What did you try exactly ?​

​

 

[Andy23]

What did you try exactly ?​

​

Hello Sir,

I tried the code below:
I honestly don't understand about VBA code as well. What I can do is to change the sheet name.
I would highly appreciate it if you could help. Thank you so much!

Sub Demo1()
    Dim V, W(), R&, L&, oCol As Collection
        V = Sheet2.ListObjects(1).Range
        ReDim W(1 To UBound(V), 1 To 4)
        On Error Resume Next
    For R = 2 To UBound(V)
     If V(R, 1) <> V(R - 1, 1) Then
        If L Then W(L, 3) = oCol.Count
        L = L + 1
        W(L, 1) = V(R, 1)
        Set oCol = New Collection
     End If
        oCol.Add Empty, CStr(V(R, 3))
        W(L, 2) = W(L, 2) + V(R, 11)
        W(L, 4) = W(L, 4) + V(R, 9)
    Next
        On Error GoTo 0
        W(L, 3) = oCol.Count
    With Sheet1.ListObjects(1)
     If .ListRows.Count < L Then
        .Resize .Range.Resize(L + 1)
        .DataBodyRange.Columns(2).NumberFormatLocal = .Range(2, 2).NumberFormatLocal
        .ActiveWorkbook.Sheets("Sheet1").ListObjects("Table2").ListColumns.Add
     ElseIf .ListRows.Count > L Then
        .Range.Rows(L + 2 & ":" & .Range.Rows.Count).Delete
     End If
        .DataBodyRange = W
    End With
End Sub

 

[Marc L]

As you wrote :​

I tried to change the edit

So in fact you did not ever try anything ! ​

As a reminder : easy to learn with using the Macro Recorder and reading VBA help.​

​

I can't remind if the original was made under 1998 version, maybe Excel 2000 …​

Anyway according to post #16 the last century VBA demonstration revisited as my last try :​

Sub Demo2()
    Dim V, W(), R&, L&, oCol As Collection
   With Sheet2.ListObjects(1).Range
       .Sort .Cells(1), 1, Header:=1
        V = .Value
   End With
        ReDim W(1 To UBound(V), 1 To 4)
        On Error Resume Next
    For R = 2 To UBound(V)
     If V(R, 1) <> V(R - 1, 1) Then
        W(L, 3) = oCol.Count
        L = L + 1
        W(L, 1) = V(R, 1)
        Set oCol = New Collection
     End If
        oCol.Add Empty, CStr(V(R, 3))
        W(L, 2) = W(L, 2) + V(R, 11)
        W(L, 4) = W(L, 4) + V(R, 9)
    Next
        On Error GoTo 0
        W(L, 3) = oCol.Count
    With Sheet1.ListObjects(1)
     If .ListRows.Count < L Then .Resize .Range.Resize(L + 1) Else _
     If .ListRows.Count > L Then .Range.Rows(L + 2 & ":" & .Range.Rows.Count).Delete
        .DataBodyRange.Columns("A:D") = W
    End With
End Sub

You may Like it !​

​