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

[MTennis]

Hey Guys,

i have an Workbook with 5 Sheets where you can add Data while Using a Userform. The Data begins in each Sheet in row 10, since i nead the rows above 10 for my interface. I have also an Mastersheet, where the Data from these specific Sheets should be copied and should be edited if needed by DoubleClick and an Unserform. I Managed to write the Code for Copying the Data from these Sheets, but I also need the Sheetname next to the Data in Column A, so it's clear where the Data is from. The Data begins also in Row 10 of the Mastersheet.

1.Problem: Can't get the Sheet names in Column A and the Data to start in Column A.
2. If one Sheet is empty it Copies the Headers into the Mastersheet
3. By Double Click in the Row my Userform doesn't appear.

I would really appreciate if someone could help me, since i just startet with vba and don't know What to do.

Private Sub Worksheet_Activate()
Dim Ws As Worksheet
Me.Rows("6:" & Rows.count).Clear
Me.Range("A9:Z9").Value = Array("Markt".... (And So on)

For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> "Summary" And Ws.Name <> "Startseite" And Ws.Name <> "Agenda" (And so on) Then
        Ws.Rows("10:" & Ws.Cells.Find("*", searchdirection:=xlPrevious).Row).Copy Me.Range("A" & Me.Cells.Find("*", searchdirection:=xlPrevious).Row + 1)
End If
Next Ws

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim Sheet As String
Dim POS As Integer

Cancel = True

If Range(Target.Address).Row >= 10 Then
    Blatt = Cells(Target.Row, 2).Value
    If IsNumeric(Cells(Target.Row, 1).Value) Then
        POS = CInt(Cells(Target.Row, 1).Value)
    Else
        POS = 0
    End If
Else
    Exit Sub
End If

If POS = 0 Then
    Exit Sub
Else
    Sheets("Code").Range("G2") = Sheet
    Sheets("Code").Range("G3") = POS
End If

Edit.Show
    
End Sub

 

[MTennis]

Hey Guys,

i have an Workbook with 5 Sheets where you can add Data while Using a Userform. The Data begins in each Sheet in row 10, since i nead the rows above 10 for my interface. I have also an Mastersheet, where the Data from these specific Sheets should be copied and should be edited if needed by DoubleClick and an Unserform. I Managed to write the Code for Copying the Data from these Sheets, but I also need the Sheetname next to the Data in Column A, so it's clear where the Data is from. The Data begins also in Row 10 of the Mastersheet.

1.Problem: Can't get the Sheet names in Column A and the Data to start in Column A.
2. If one Sheet is empty it Copies the Headers into the Mastersheet
3. By Double Click in the Row my Userform doesn't appear.

I would really appreciate if someone could help me, since i just startet with vba and don't know What to do.

Private Sub Worksheet_Activate()
Dim Ws As Worksheet
Me.Rows("6:" & Rows.count).Clear
Me.Range("A9:Z9").Value = Array("Markt".... (And So on)

For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> "Summary" And Ws.Name <> "Startseite" And Ws.Name <> "Agenda" (And so on) Then
        Ws.Rows("10:" & Ws.Cells.Find("*", searchdirection:=xlPrevious).Row).Copy Me.Range("A" & Me.Cells.Find("*", searchdirection:=xlPrevious).Row + 1)
End If
Next Ws

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim Sheet As String
Dim POS As Integer

Cancel = True

If Range(Target.Address).Row >= 10 Then
    Blatt = Cells(Target.Row, 2).Value
    If IsNumeric(Cells(Target.Row, 1).Value) Then
        POS = CInt(Cells(Target.Row, 1).Value)
    Else
        POS = 0
    End If
Else
    Exit Sub
End If

If POS = 0 Then
    Exit Sub
Else
    Sheets("Code").Range("G2") = Sheet
    Sheets("Code").Range("G3") = POS
End If

Edit.Show
   
End Sub

When i write The Sheet Name in Column B of the Mastersheet, the Data gets edited. But The Mastersheet should refresh itself when the editing is done