Macro to build a table from multiple sheets

I have a workbook with over 100 sheets. I am trying to build a table from data on the sheets.

The invoice number is always in the same cell
The data always start in the same row but have variable number of rows populated.

I can sort of getting it working but I can't get the 'goto next sheet' bit to work.

Thanks for any assistance. I've attached a sample workbook.

Hi alansidman

Each invoice has the number in cell G9.

This is a sample I've created and not the original which has sensitive information. That one has the macro but it doesn't work.

Specifically, I'm trying to build a table - in the Sample it's the front sheet - with the sales data from all the sheets in the workbook.

Exactly what I wanted! I now understand where I went wrong with my code.

Thanks - I'm grateful for your help.