Copy data from one sheet to another (special criteria)

Hi VBA Experts,

Not new to this forum but very new with VBA.

I have a report extracted from a system but the output format makes it difficult for data manipulation. If order to do that, I need to arrange data to a specific format.

From the attached file:

CopytoAnotherSheet.xlsx

I like to copy data from Col A, B and C from Sheet 1 and represented in a nice order to Sheet2
I have started writing out the steps but finding it hard to continue.

Hope experts out there can offer some assistance or point me to right direction.

Cheers

Hi there,

Thanks for coming back. All the information I have provided is made up but format wise is 100%. Actual report is over 2800 lines

1. There is not real significance with NSW. (FYI, NSW is a state in Australia) I have included as there are some companies with inclusion of symbols (e.g. " - " " () " ..etc)
2. The export can vary each month. (e.g. Additional/deduction company name, additional/deduction product, additional/deduction product range) but format wise it will still be the same.
3. The report is standard, but with inconsistent programming. What I can distinguish is:

Product total ends with - "Total for :" (no space after colon) followed by product name
Company total ends with - "Total : for " (space after after for) followed by company name

I am currently attempting to do a loop ( Do..if.loop until Grande Total: ).. but a bit tricky.. so trying to find my way around it. Let me know if I am using the right technique. Was not expecting this to be that difficult. Any guidance would be great.

Thanks

Hi Jaslake,

Thanks for responding. Don't worry about the bold fonts, I put it there to make it stand out. Actual report, all cells are unbold.
I have made quite a lot of progress with the macro but now slowly fine tuning it. Maybe is not the best method but appears to work with a few more steps.

"daffodil11" - I did pick up a small mistake (one of the Total line) with my sample file but have since fixed.

In the new file - Please refer to Sheet "Working"

1. In Sheet Working - activate macro copy (This will extract a list in the same sheet, output Company, Product, Range, Sales values)
2. still working on it.. copy Col I to L to Sheet "Extract" Col A to D. (Ideally when the entire code is complete, it wouldn't even need this step as I want step 1 to be in extract sheet)
3. Select sheet "Extract", Col (C:D) only (i.e. Range & Sales), Go to --> Special --> Blanks --> Delete entire row (I tried recording a macro but it failed to work?) Manually is quite easy but automating it would be best.
4. Select Col(C:C) (i.e. Range only) - Find word "Total" and delete rows --> Have a working codes for this, execute macro "delete_rows". However, to be safe I like to have a code to find "Total : for" and "Total for :". Can this be done in one code?
5. Mission accomplished (First part of it for my purpose)

I have created quite a few codes to make this happen, it doesn't look good I know. Is it normal to have individual codes everywhere? I guess, is still workable to start code 2 when code 1 is finished. Hope you can enlighten me with a better approach. Newbie with VBA but eager to learn.

Here is the new file - CopytoAnotherSheet-1.xlsm

Cheers