Extracting table from data within Excel using VBA

[aminex]

Hello everyone,

I have some data, in a xlVeryHidden sheet, with multiple headers. An example is given below.

I would like to auto-generate a table on a different sheet with only revelent headers and filter by source and month/year.
Basically, I want to write a macro that would make this table for, say, January and source1 only, without headers 1, 2 or 3.

(Very similar to the Pivot table, only without combining the data)


I would appreciate any help I can get


--------------------------------------------------------------------------------------------------------
[TABLE="width: 723"]
<colgroup><col span="4"><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]Source[/TD]
[TD]Type[/TD]
[TD]Ref[/TD]
[TD]Date[/TD]
[TD]Id[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Debit[/TD]
[TD]Credit [/TD]
[/TR]
[TR]
[TD]Source1[/TD]
[TD]Sales[/TD]
[TD]FGGH89[/TD]
[TD="align: right"]5-jan.-14 [/TD]
[TD]Customer1[/TD]
[TD]info 1[/TD]
[TD]info 5[/TD]
[TD]info 9[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Source2[/TD]
[TD]Sales[/TD]
[TD]0121/8987[/TD]
[TD="align: right"]1-jan.-14 [/TD]
[TD]Customer2[/TD]
[TD]info 2[/TD]
[TD]info 6[/TD]
[TD]info 10[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Source1[/TD]
[TD]Bill[/TD]
[TD]21200197[/TD]
[TD="align: right"]6-jan.-14 [/TD]
[TD]Provider1[/TD]
[TD]info 3[/TD]
[TD]info 7[/TD]
[TD]info 11[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]13,356.72[/TD]
[/TR]
[TR]
[TD]Source1[/TD]
[TD]Purchase[/TD]
[TD]SSQ2013[/TD]
[TD="align: right"]2-feb.-14 [/TD]
[TD]Provider2[/TD]
[TD]info 4[/TD]
[TD]info 8[/TD]
[TD]info 12[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]257.40[/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------------------------------------------------------------------------------

 

[aminex]

I have found a workaround using Advancedfilter that copies filtered data to a new workbook, then deleting unwanted columns.