Get Data From Sheets to MasterSheet and keep All sheets updated if Something is updated

[MTennis]

Hey Guys,
I'm new To VBA und English is not my Motherlanguage
I'm Currently Working on a Code that should get Data from some Specific Sheets and put them in a Mastersheet by lisiting the SheetName Next To the Data but Somehow it doesn't work
I Have Sheets named after the Market the Cars are from and beginning in Row 10 from A to X the Data of each Car is listed. The Mastersheet beginns also in Row 10 and the Headers are the Same but I need The SheetName in Column A and the Data beginning from Column B. The Sheets should update themnselves when something is edited in the SummarySheet oder the Sheets where The Data is from. The User fills out A usefrom which create new Data and can edit the existing Data. Im Not Sure if the FindLastRow Stuff really Works, because all the Data starts in Row 10 Since I have use Row 1-9 As Placeholders for my Buttons.
Im Rellay Struggeling with it and can't find any Solution. So I would appreciate every Help I can get

Sub GetDataFromSheets()
Dim j As Integer
Dim ws As Worksheet
Dim Summary As Worksheet, Sheet As Worksheet
Dim Monthcol As Long, LastSummaryRow As Long, Lastrow As Long, Index As Long, cnt As Long
Dim Source As Range, Target As Range

Set Summary = ThisWorkbook.Worksheets("Gesamtübersicht2")
Summary.Range("A9:Z9").Value = Array("Markt", .... And So On
Monthcol = 1
LastSummaryRow = FindLastRow(Summary)

For Each Sheet In ThisWorkbook.Worksheets
    If Sheet.Name <> "Summary" And Sheet.Name <> "Startseite" And Sheet.Name <> "Agenda" And Sheet.Name <> "Gesamtübersicht" And Sheet.Name <> "Gesamtübersicht2" And Sheet.Name <> "Archiv" And Sheet.Name <> "SHED_Messkopf" And Sheet.Name <> "Aushängeschild_Brasilien" And Sheet.Name <> "Aushängeschild_Korea" And Sheet.Name <> "Code" Then
        With Sheet
            cnt = Sheet.Range("A10", Sheet.Cells(Rows.count, "A").End(xlUp)).count
            Lastrow = cnt
            Set Source = .Range(.Cells(10, Monthcol), Cells(Lastrow, 26))
        End With
        With Summary
            Set Target = .Range(.Cells(LastSummaryRow + 1, Monthcol + 1), .Cells(LastSummaryRow + 1 + Lastrow, 26))
            Source.Copy Target
            Index = FindLastRow(Summary)
            While .Cells(Index, 1) = ""
                .Cells(Index, 1) = Sheet.Name
                Index = Index - 1
            Wend
        End With
        LastSummaryRow = FindLastRow(Summary)
    End If
Next Sheet

End Sub

Public Function FindLastRow(flrSheet As Worksheet) As Long
Dim cnt As Long
Dim Sheet As Worksheet
    If Application.WorksheetFunction.CountA(flrSheet.Cells) <> 0 Then
        FindLastRow = flrSheet.Cells.Find("*", Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        cnt = Sheet.Range("A10", Sheet.Cells(Rows.count, "A").End(xlUp)).count
        FindLastRow = cnt
    End If
End Function

 

[Automatrix]

Have you considered using Power Query for this instead? It is good at drawing data from multiple other sheets using the "From folder..." data source.

 

[MTennis]

Have you considered using Power Query for this instead? It is good at drawing data from multiple other sheets using the "From folder..." data source.

The Data is in the Same Workbook. But I have not Considered Power Query for this since I didn't even know anything about it.

 

[Automatrix]

The Data is in the Same Workbook. But I have not Considered Power Query for this since I didn't even know anything about it.

I highly recommend trying it out. If your data is in the same workbook, you may have to make a connection to each of the data tables.

 

[MTennis]

I highly recommend trying it out. If your data is in the same workbook, you may have to make a connection to each of the data tables.

Will it automatically change the Range where the Data is from, when new Data is added ?

 

[Automatrix]

Will it automatically change the Range where the Data is from, when new Data is added ?

Yes. It does not operate on ranges, but on Tables, which auto-expand to include new data.

 

[MTennis]

I'm unsere if my Useforms would Work If I transform the Sheets into Tables. Is there No Way to change the Code that it would work as Expected ?

 

[Automatrix]

I'm unsere if my Useforms would Work If I transform the Sheets into Tables. Is there No Way to change the Code that it would work as Expected ?

I'm afraid I can't help with VBA (Power Query does all I need). How many sheets are you working with?

 

[MTennis]

I'm afraid I can't help with VBA (Power Query does all I need). How many sheets are you working with?

5 to 6

 

[Automatrix]

You could place the workbook in a folder, then connect to that folder (which does not require the data to be in tables) with 5-6 individual queries.