Need function to count rows until trigger, use variable to specify cell range to copy

I'm attempting to do two things in an Excel workbook, which I will believe require the use of at least one, if not two macros, but I'm clueless as to how to go about this.

The first step is creating some sort of macro to count cells in one column. The count would always begin at the same cell (A17) of a sheet that will always be titled 'Segments'. The count should begin with a stored variable of 1, and add 1 for every subsequent cell in the column it checks that does not include the words 'Total Revenues'. When it reaches these words, the count should not add 1, and cease counting, saving the variable.


The second step is using the number that the counting macro reached to output the content of the rows 17 to (17+n) into a separate sheet in the workbook. Similarly, I would like to be able to do this not just for column A, but for any number of columns. For example, rows 17 to 17+n for columns A, B, C and D will be copied to another sheet, becoming rows 7 to 7+n, for the same columns.

Overall, the function will:

a) Determine how many rows in Column A contain relevant data
b) Export the data from several columns on the sheet to another parallel sheet, with the correct number of rows


Strictly speaking, this operation could be done without the first step, if I was aware of how to do the second step, but have the number 'n', calculated by the macro, be replaced with a manually input number in a 'variable' cell.

Either way, I'm clueless as to how to do this. Can anyone help? It would be much appreciated.

Thanks in advance!