Copy Data from multiple sheets to Main datasheet

Hi Bogleda,

This should get your CF:

Sub Bogleda2(): Dim wt As Worksheet, ws As Worksheet, t As Long, r As Long, i As Long, D As Range
Application.EnableEvents = False: t = 3: Set wt = Sheets("Table of Context"): wt.Activate
wt.Range(Cells(t, 1), wt.Cells(Rows.Count - t, 1)).EntireRow.Clear
For Each ws In Worksheets
If ws.Name = wt.Name Or ws.Name = "CONVERSIONS" Or ws.Name = "DropDown Menus" Then GoTo GetNext
r = 4: Do Until ws.Range("A" & r) = "": r = r + 1: Loop: r = r - 1
For i = 4 To r: ws.Cells(r, 6) = ws.Name: Next i
Set D = ws.Range(ws.Cells(4, 1), ws.Cells(r, 6)): r = r - 3
D.Copy wt.Range("A" & t)
t = t + r: wt.Columns.AutoFit: wt.Rows.AutoFit
GetNext: Next: Application.EnableEvents = True: End Sub

And - when you're building the full book, you could just Change the name of the event code in the ThisWorkbook module:

Private Sub XWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
                Set Sh = ActiveSheet
If Sh.Name = "Table of Context" Or Sh.Name = "CONVERSIONS" Or _
Sh.Name = "DropDown Menus" Then Exit Sub
If Target <> "" And Target.Column < 6 Then
Dim wt As Worksheet, X As String, FN As String, F As Range, D, r As Long
Set wt = Sheets("Table of Context"):  r = Target.Row: X = Cells(r, 1)
If Target.Column = 1 Then X = Cells(r, 2)
D = Range(Cells(r, 1), Cells(r, 6)): D(1, 6) = Sh.Name
Set F = wt.Range("A:B").Find(X)
If Not F Is Nothing Then
Application.EnableEvents = False
wt.Range(wt.Cells(F.Row, 1), wt.Cells(F.Row, 6)) = D
Application.EnableEvents = True
End If: End If: End Sub