Autofill, Sorting, VBA

[lorena.]

Hi everyone



I need help accomplishing this automation task. I guess the only possible way is to use VBA, which is why I am asking for code suggestions. However if somebody knows how to implement it withou VBA feel free to share.



(You can find the file attached)



What I want to do:

1. When starting the macro it should first update the table by marking the first red row 2 and dragging it down until row 20 (without overwriting row 21/the row with "Total") (Autofill without formatting)

2. Since this autofill fills the table with other stuff which is below the last name I want to delete everything starting from the yellow "Total". So it should somehow find in column A the word "Total" and delete everythin below it (all the green values)

3. Finally it should sort the light blue marked sub-table. In general it should select and sort only the sub-table where the first yellow row appears (it should really find the colored cell since this is not static)

I would much appreciate help, since I need to automate this and I don't know how.

ex.JPG

General improving tips appreciated!

Thank you!

[MarvinP]

Hi Lorena,

If your data was input in a way like in the attached, you could do a Pivot Table and easily get the result you might like better. Lots of answers are based on the way the original data is entered. See the attached for my suggestion.
Map reformat for PT.xlsx

[lorena.]

Hi,

How do I need to implement the above mentioned steps 1-3 if my input data was in this format?