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.
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.
VBA Code:
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
Code:
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