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

MTennis

New Member
Joined
Dec 7, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

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
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
 
Upvote 0

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top