Make a simple report with VBA

[Rayman]

Hi all members of this helpfull forum,

I attached a simple example file with 2 sheets , one with with data and one with the result that i want to obtain with a macro, not with a formula or using excel filter.

The name in column "Type" are variable
In sheet "Data" there are blank rows (as in the example)
The name in "UM" column are variable
The sheets names that contain data is variable but are all sheets of one workbook
I want obtain the result of the active sheet in the workbook, and write and show them in a listbox with 3 columns (as a alternative, write them in a new worksheet as in the example, worksheet to add at the active workbook)

I try to do this for myself for ours with no look, i lost me in a lot of if, then ,for each, do while, ecc.

Any help will be really appreciate.

[shrivallabha]

A formula can be used. In cell C2 of Report, insert this formula and then copy it down.

=SUMPRODUCT(--(Data!$A$1:$A$106=Report!$A2),--(Data!$B$1:$B$106=Report!$B2),Data!$C$1:$C$106)

This formula is explained in great detail by XLD in the sub-forum SUMPRODUCT formula.

On second thoughts: You can use Pivot Table also.

[Rayman]

A formula can be used. In cell C2 of Report, insert this formula and then copy it down.


This formula is explained in great detail by XLD in the sub-forum SUMPRODUCT formula.

On second thoughts: You can use Pivot Table also.

Thanks for reply, but the attached file is only an example.
In my working workbook i have several sheet, with variable name from wich take the data. The user also can delete or add new sheet with new variable name to my workbook....

Good evening

[Rayman]

Well, in the new attached file, i have a form with a ListBox filled with data taken from sheet "Data" , column "F", without duplicate.

1 problem:

I dont understand why the column2 of the list box is not filled with the data taken from column "G" of the sheet "Data". What is wrong in the code??

Once the problem will be solved what remain to do is:

in Column 3 of the ListBox i need to show the total sum of each materials (e.g. cemento= q.li 196, Misto = mc. 166, etc)

Thanks in advance for help.

[shrivallabha]

Part AA]
I tried Pivot Tables in VBA for the first time. They were harrowing experience when I did them 2 years back (without VBA that is and I did not know of forums then) . So while I was slogging it out with the first workbook, your 2nd version appeared (which I was dreading). But by then I had finished with the coding for the first case. Editing it was a little easier.

Steps:
1. Insert a worksheet named "Report"
2. Goto VBE, insert module and then paste following code.
[vba]Public Sub CreateReport()
Dim pCache As PivotCache
Dim pTable As PivotTable
Dim lLastRow As Long
Dim ws As Worksheet
Set ws = Sheets("Data")
lLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, ws.Range("A3:H" & lLastRow))
Set pTable = pCache.CreatePivotTable(Sheets("Report").Range("A" & Rows.Count).End(xlUp)(4), _
"Report No." & ws.Name, True)

With pTable

'To get old format for rearranging the info
.InGridDropZones = True
.RowAxisLayout xlTabularRow

'Adding the first field at pos 1
With .PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With

'Adding the second field at pos 2
With .PivotFields("u.m.")
.Orientation = xlRowField
.Position = 2
End With

'Adding sum of the data
.AddDataField .PivotFields("Quantity"), "Sum of Quantity", xlSum

'Removing subtotals which are not needed
.PivotFields("Type").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)

'Removing blanks
.PivotFields("u.m.").PivotItems("(blank)").Visible = False

End With
End Sub

[/vba]

Part BB]
The above code will work. However, there are some grey areas which I do not understand. Here it is:

This works:
[vba]Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, ws.Range("A3:H" & lLastRow))[/vba]
while this doesn't:
[vba]Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, ws.Range("A3:P" & lLastRow))[/vba]
I noticed one column was having duplicate name (Descrizione) but changing that didn't stop the error.

PartCC]
I have seen your requirement for listbox to which I will give a try but they have always been my bete noire so expect an even more delayed response . There are many things in VBA I fear

[shrivallabha]

I do not know if you will find this solution as acceptable.

1. Sheet Report is hidden where the solution provided (Pivot) above generates a report.

2. Then using Listbox's Rowsource property gives the desired output.

3. I know NO italian so I have changed one button (CmdInserisci) to show the userform. Press it and userform will appear. I don't know if it had something against it before.

My explanation might be bad so I'm attaching the changed workbook.

[Rayman]

I do not know if you will find this solution as acceptable.

1. Sheet Report is hidden where the solution provided (Pivot) above generates a report.

2. Then using Listbox's Rowsource property gives the desired output.

3. I know NO italian so I have changed one button (CmdInserisci) to show the userform. Press it and userform will appear. I don't know if it had something against it before.

My explanation might be bad so I'm attaching the changed workbook.

Hi, shirvallabha, many thanks for your effort.

i have to go out now, Ill try your solution tomorrow and let you know

Many, many thanks and ... my english is so bad as your italian

[Rayman]

I do not know if you will find this solution as acceptable.

1. Sheet Report is hidden where the solution provided (Pivot) above generates a report.

2. Then using Listbox's Rowsource property gives the desired output.

3. I know NO italian so I have changed one button (CmdInserisci) to show the userform. Press it and userform will appear. I don't know if it had something against it before.

My explanation might be bad so I'm attaching the changed workbook.

I tried your solution and it works very well.

I have change your code from -Set ws = Sheet("Data")- to -Set ws = ActiveSheet so it work with the active sheet independently of the sheet 's name.
however, your code is a "workaround" , I like to write the data directly in the list box but it seems a very difficult thing .

A question for you o for other member of the forum
Can you understand why this code does not write the values in 2° column of listbox1 (Lst1 in the code) but fill ok column 1 ???
[vba]Private Sub UserForm_Initialize()
Dim col As Collection, col2 As Collection
Dim v As Variant, v2 As Variant
Dim sht As Worksheet
Dim lng As Long
Set col = New Collection
Set col2 = New Collection
Set sht = ActiveSheet
On Error Resume Next
With ActiveSheet
For lng = 4 To 500
If Cells(lng, 6) <> "" Then
col.Add .Cells(lng, 6).Value, _
CStr(.Cells(lng, 6).Value)

If Cells(lng, 7) <> "" Then
col2.Add .Cells(lng, 7).Value, _
CStr(.Cells(lng, 7).Value)
End If
End If
Next
End With
With Me.Lst1
Lst1.Clear

For Each v In col
Lst1.AddItem v
Next
For Each v2 In col2
Lst1.Column(2).AddItem v2
Next
End With
Set col = Nothing
Set col2 = Nothing
Set sht = Nothing
End Sub
[/vba]

anyway, Many thanks for the solution that I will use from now until I can find a best way.