VBA - search specific value in cell and copy 10 lines (downward and sideward)

Hi,

I've recorded a macro where the pivot table will automatically constructed. However, the raw data is not always the same (total lines are different) therefore the grand total sum will not always be on the same line (refer below hard code range HC5 and B386). If this is a variable, how do I make the macro to search for the "grand total" word, sort from largest to smallest numbers and copy the next 10 cells (downward and sideward, separately of course.)

The reason I want to copy due to the template that I want to paste the data into the template.

Please Login or Register  to view this content.

I have attached the image foryour ease of reference.

sample1.jpg
Many thanks.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here