Help Needed: filtering data from to sheets in another sheet currency wise

[khaledocom]

Dear friends,

Please find the attached herewith XL file, I have explained every thing in.
Appreciate your support with this regarding.
Thanks and have a great evening.

[p45cal]

In the attached, on the Filter sheet in the vicinity of cell E19 is a pivot table showing the data you want, sorted as you want.
The pivot table source data is on a new sheet Sheet1 where I have manually merged the tables from sheets B7 and A22.
The trouble is automating the process of merging the two tables:

While I can come up with convoluted code to process the sheets in your sample file I suspect it would fall over very quickly with real world sheets. (Creating code to create pivot table is straightforward - almost). A bit more of a 'real-world' sample workbook might be needed, one without comments, added rows/columns, field numbering etc. which we already have in your first sample workbook.

Some of the problems:
1. Is there always a completely blank row below the headers (or is this just your sample file)?
2. The tables seem to be randomly placed within each sheet, so no consistency there.
3. There are completely blank columns within one of the tables (which makes it difficult to use currentregion).
4. The headers are different - and you helpfully numbered them (but is this the case with your actual tables?)… Worse, the Filter sheet has a header Vendor (Field 5) sourced from headers Vendor name on the source sheets, but you have the same header (Vendor) as field 4 on the B7 table destined for a field headed Account! All quite confusing.
5. You want merging of 2 fields from A22 table but nothing merged from B7 table, so the source sheets need treating quite differently; is there a characteristic of the sheets that can be used to identify how they should be treated?
6. How does the data get into the source sheets? Is it via a query of some sort? I see there are lots of Connections in the workbook. It might be we can identify the tables robustly from those.
7. This might be solved if the two (more perhaps?) sheets are always going to be called B7 and A22 but I suspect not.

[khaledocom]

Thanks lot Sir for your great help, but I would like to inform you that:
1- The data available in the two sheets are being imported from SAP directly, Blank rows and columns will be always available.
2- VBA code is needed and it's more helpful for me than pivot table for same and I confirm the consistency of data or information imported every time.
Again I do appreciate your help with this regarding and still looking forward to your support.

[p45cal]

but I would like to inform you that:
1- The data available in the two sheets are being imported from SAP directly, Blank rows and columns will be always available.

That's unfortunate.

2- VBA code is needed and it's more helpful for me than pivot table for same

VBA code and Pivot tables are not mutually exclusive.
In the attached is some code that creates a new merged table on a new sheet from the two existing tables (you could just autofilter this table by the way). It also creates a new Filter sheet with a pivot table on, where you can change the currency at will.
The downside to this code is that it won't work in the real world - much of it is hard coded to your specific tables - I just wrote it to show what I said before: "While I can come up with convoluted code to process the sheets in your sample file I suspect it would fall over very quickly with real world sheets."
So again, a bit more of a 'real-world' sample workbook will be needed, one without comments, added rows/columns, field numbering etc. which we already have in your first sample workbook.

and I confirm the consistency of data or information imported every time.

If the data really is consistent, which I very much doubt, then the code will work every time!

In the attached workbook, run the macro blah (or click the button near cell E2 on the renamed sheet FilterOld (this sheet is no longer required)).