Data Manipulation

Just a couple of quick questions;

1) Are the column headers always the same? ie. there will always be P1 -> P12 followed by a sales column?
2) Do you want the 2 blank lines between each vendor?

Ok, the code is as follows to work on the data you have supplied here.
I have your 'Input' data on a sheet called "IN2" and that is all there is on that sheet.

Please Login or Register  to view this content.

I have coded this using 3 constants at the top of the code because I have an 'insight' as to where this might be heading

SRC_CUST_COL indicates the column that the CUST data appears in on the Source sheet.
SRC_START_ROW indicates the row that the macro should start at looking for customer data on the Source sheet.
SRC_WS_NAME indicates the name of the Source worksheet.

I have attached a spreadsheet that serves 2 purposes.
If you run the 'Create_Report' macro on the workbook as it is attached, then it will take the data from "IN2" which is formated as per your exmaple in this thread, and it will create the REPORT worksheet based on that.

HOWEVER, the Workbook also contains 2 extra sheets, "IN1" which is a copy of the 'raw' data from your previous thread, and also "IN1 (Backup)". "IN1 (Backup) is simple used as a place you can COPY the raw data from and paste back into "IN1" after running the macro if you want to run it again to see what it is doing.
As mentioned the 'Create_Report' macro has 3 constants at the top that control where the macro looks for it's source data. You can change these as here:

Please Login or Register  to view this content.

then run the 'Process_Data' macro. This will call the original macro to remove the 'raw data' headers and the blank rows on "IN1", then create the report from that data.
I have included this so that you can see how it can all fit together if required.

I hope it makes sense, if you have any questions please do ask

The reason that when you run 'Process_Data' it is because of the way you are detecting which lines to delete.
Take the first 'customer' in the example you have uploaded:

"TOWSWWSWSYWSWSWSW"

When you are looking for lines to delete you have defined your search patterns thus:

Please Login or Register  to view this content.

If we look at this carefully...

"TOWSWWSWSYWSWSWSW"

Please Login or Register  to view this content.

So you can see from above that the cell with the customer name in matches the search pattern, and so that line will be deleted, losing 1 line of customer data.
Although this is a 'mock up' so that you could upload an example I would guess that maybe you have a customer name that matches one of the search patterns.
The easy way to find out is to select the "IN1" tab, and then run the "Delete_Report_Header_And_Blanks()" Macro. You will then need to scroll down through the data to see if anything doesn't seem to follow the expected pattern.
As I said before I would advise you get rid of the "*" in the search string as they are not needed, (I may have inadvertantly copy & pasted them from one of your examples!). You can then look closer at your patterns.

Sticking with the same example for instance I presume the "To" line that you are trying to remove is similar to the one on row 3 of your example:

"01/04/2008 To"

but you dont want to delete:

"TOWSWWSWSYWSWSWSW"

To overcome this you could modifier your search pattern to look for " To" (note the prefixed space).

When you want to match data, whether you are trying to INCLUDE, or EXCLUDE it, it is always a good idea to include as much of the fixed data as possible to try and avoid incorrect matches.

I have looked at your raw data and I believe changing your search pattern to this will serve you well:

Please Login or Register  to view this content.

NOTE There are 2 different types of --- on your raw data, one of which the forum can not display. For that reason I have modified the search pattern in the attached workbook, do NOT just copy and paste it from above.

It also seems that your raw data contains one cell in the 1st column that contains just " ", (a single space). Due to this although the cell LOOKS blank, it does not get deleted by the routine that deletes the blank rows.
To cope with this I have now thrown in an EXTRA "Find" case purely to look for rows where that first cell contains a single space, and then delete the row. Also note that when search for the spaces the find function is using "LookAt:=xlWhole" so that it only matches if the entire cell contents matches " ". This stops it matching a cell that contains a space, but also other text, such as "My Company".

The complete code for the 'Delete_Report_Header_And_Blanks()' function now looks like this, but remember use the one in the attached workbook, not copy and paste due to the problems with the dashes

Please Login or Register  to view this content.

Hopefully that should either fix it or get you a lot closer