VBA - create a new table from a datasource dependent on values in datasource

fergie_excel

New Member
Joined
Jun 6, 2017
Messages
1
Hello,
I've been struggling with this problem for nearly a week and any help would be fantastic.

I have a data source with a large number of columns:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref No
[/TD]
[TD]Date
[/TD]
[TD]other1
[/TD]
[TD]other2
[/TD]
[TD]other3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]Scheduled
[/TD]
[TD]Sent
[/TD]
[TD]timestamp
[/TD]
[/TR]
[TR]
[TD]000
[/TD]
[TD]11/03/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yes
[/TD]
[TD]yes
[/TD]
[TD]1344
[/TD]
[/TR]
[TR]
[TD]001
[/TD]
[TD]12/03/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no
[/TD]
[TD]yes
[/TD]
[TD]1216
[/TD]
[/TR]
[TR]
[TD]002
[/TD]
[TD]13/04/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yes
[/TD]
[TD]no
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003
[/TD]
[TD]14/04/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no
[/TD]
[TD]yes
[/TD]
[TD]1432
[/TD]
[/TR]
[TR]
[TD]004
[/TD]
[TD]15/05/17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no
[/TD]
[TD]no
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


each ref no is report that gets sent via email by a user doing so by scheduling it via an excel user form I have made.
When the user schedule's the report to be sent the row and column relating to it is also updated to show whether it has been scheduled or already sent, in some cases scheduled to be sent for a second time (show by both columns value = yes), the time stamp is updated at the point of being sent.

from the data source (Table1 - also dynamic) I need to display in a separate table only those reports that are currently scheduled but is automatically updates whenever a reports scheduled status is changed.

Table2 only needs to list the report ref number, the date, scheduled, sent and time stamp columns.

Table 1 is contained on a sheet called "all_reports"
Table 2 would be contained on a separate sheet labelled "next_sched"

if its possible, I would like to bypass making the separate table and sheet all together and have the data displayed in a listbox with 5 columns on a userform.

Currently I have a listbox (called "AllReports") which displays table1 on the userform, But I have no idea how to display only specific columns and rows based on a criteria.

Hopefully I've described this ok, Any tips and advice would be great, If anymore info is needed please ask.

Thanks in advance

Fergie
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi and welcome to the MrExcel Message Board.

This should give you a starting point:
Code:
Private Sub UserForm_Initialize()
    Dim ary     As Variant
    Dim dic     As Object
    Dim iRows   As Variant
    Dim i       As Long
    
    With ThisWorkbook.Worksheets("Sheet5")
        ary = .UsedRange
    End With
    
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(ary)
        If ary(i, 2) = DateValue("13/04/2017") Or i = 1 Then
            dic(i) = Array(ary(i, 1), ary(i, 2), ary(i, 12), ary(i, 13), ary(i, 14))
        End If
    Next

    With ListBox1
        .ColumnCount = 5
        .List = Application.Index(dic.items(), 0, 0)
    End With
End Sub
It is some code for the Userform and it runs when the user form is initialized.
I filtered the rows using the date. You can change that.
Note the "i = 1" part of that line is there to include the column headings.
I have assumed that you are using Windows so that I can use a Dictionary object.
Each dictionary item is an array and each array is one row of the output.

The data is read in using .UsedRange. That will include all the rows and columns on the page.

The line:
Code:
.List = Application.Index(dic.items(), 0, 0)
populates the listbox list. "Application.Index" is used to transfer all the items in the dictionary, which are all arrays. The "0, 0) means all rows and all columns in the dictionary. "dic.items()" is effectively an array and because each row is another array it is effectively a 2D array.

I had my input data on Sheet5. You might need to change that.

I hope this makes sense.


Regards,
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top