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


--------------------------------------------------------------------------------------------------------

Source	Type	Ref	Date	Id	Header 1	Header 2	Header 3	Debit	Credit
Source1	Sales	FGGH89	5-jan.-14	Customer1	info 1	info 5	info 9	5,000.00	0.00
Source2	Sales	0121/8987	1-jan.-14	Customer2	info 2	info 6	info 10	10,000.00	0.00
Source1	Bill	21200197	6-jan.-14	Provider1	info 3	info 7	info 11	0.00	13,356.72
Source1	Purchase	SSQ2013	2-feb.-14	Provider2	info 4	info 8	info 12	0.00	257.40

<colgroup><col span="4"><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

--------------------------------------------------------------------------------------------------------

 

[aminex]

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