update final sheet with data from existing sheets

[Barney77]

Hello,

I hope somebody can and want to help me solve my challenge..

I have a workbook within many sheets. Sheets are created according a template sheet whereby name is picked from a sourcesheet. all with VBA
after creation i'am already able to copy a range of data to a Final worksheet with a second VBA code

This final worksheet contains a summary of every sheet. by creating this i copy and paste a few rows and select for every sheet the first empty row at the final sheet before pasting it
After this action the final worksheet contains for every sheet the same amount of rows with the same data. For example sheet 1: matches row 1 - 6 and sheet 2 matches row 7-12
Creation fo the sheet is a one time job.

Now I want to add the possibillity to update the data at this final sheet.
So i'am looking for vba code to copy from each worksheet that exists the range G0:G5 and paste the actual value into the range according this sheetname on the final sheet. (so for sheet 2 it should be pasted into G7:G12 because A7.value = sheet 2

For me as dummy it seems i have to check every worksheet or its name is matching a cell value on the final sheet and if so copy range G0:g5 and paste it behind the matching cell ( row unknown, range simulair to the source )

Can anybody help me with this?

[arnelgp]

use PowerQuery to create separate tables from your worksheet.
note that you need to convert the sheet to Table first.
after you make each table from each sheets, use the Append command
on the Ribbon to combine them all.
Whatever you add, modify, delete cells on the original sheets, will
immediately be reflected to the combined table.

[parttime_guy]

Kindly attach a sample file with results required ....would be of great help