Consolidating worksheets into a master worksheet

Please find below, a macro written by me to consolidate worksheets into a master worksheets all within the same workbook. I look forward to suggestion of making it work as is not at the moment. Many thanks.