helplessnoobatexcel
New Member
- Joined
- Dec 15, 2023
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
Hi All,
I have this code in which it would split the master sheet automatically based on values in a specific column and the worksheets would be named based on values in the column. Changes would be shown on the split sheet after changes has been made to the master sheet, but I would also like it to be vice versa. Meaning, that if changes are made to specific cells in a split worksheet, it would automatically update the master sheet to display the new values. Any idea on how I can do this? Thanks!
Option Explicit
Public Sub SplitTableToSheets()
Dim full_data_listobject As ListObject
Dim selected_range As Range
Dim selected_data As Variant
Dim selected_data_header As String
Dim items_sheet As Worksheet
Dim items_range As Range
Dim item As String
Dim i As Integer
Dim new_item_sheet As Worksheet
DeleteSheetWithoutWarning "items"
Set full_data_listobject = ThisWorkbook.Worksheets("full data").ListObjects(1)
' get the range currently selected
Set selected_range = Selection
selected_data_header = Selection.Offset(-1, 0).Cells(1, 1)
Set items_sheet = ThisWorkbook.Worksheets.Add
items_sheet.Name = "items"
selected_range.Copy
items_sheet.Range("A1").PasteSpecial xlValues
Set items_range = items_sheet.Range("A1").CurrentRegion
items_range.RemoveDuplicates 1, xlNo
'loop through each item and create a new sheet with the filtered data
For i = 1 To items_range.Rows.Count
If items_range.Cells(i, 1) <> "" Then
item = items_range.Cells(i, 1)
DeleteSheetWithoutWarning item
full_data_listobject.Range.Copy
Set new_item_sheet = ThisWorkbook.Worksheets.Add
With new_item_sheet
.Name = item
.Range("A1").Formula2 = "=" & full_data_listobject.Name & "[#Headers]"
.Range("A2").Formula2 = "=FILTER(" & full_data_listobject.Name & _
"," & full_data_listobject.Name & "[" & selected_data_header & "] = """ & item & """)"
''optionally uncomment these two lines to paste the results as values in the destination sheet
'.Range("A1").CurrentRegion.Copy
'.Range("A1").PasteSpecial xlPasteValues
End With
Else
Exit For
End If
Next i
End Sub
Private Sub DeleteSheetWithoutWarning(sheet_name As String)
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(sheet_name).Delete
On Error GoTo 0
Application.DisplayAlerts = True
End Sub
this is the current code i've gotten online
I have this code in which it would split the master sheet automatically based on values in a specific column and the worksheets would be named based on values in the column. Changes would be shown on the split sheet after changes has been made to the master sheet, but I would also like it to be vice versa. Meaning, that if changes are made to specific cells in a split worksheet, it would automatically update the master sheet to display the new values. Any idea on how I can do this? Thanks!
Option Explicit
Public Sub SplitTableToSheets()
Dim full_data_listobject As ListObject
Dim selected_range As Range
Dim selected_data As Variant
Dim selected_data_header As String
Dim items_sheet As Worksheet
Dim items_range As Range
Dim item As String
Dim i As Integer
Dim new_item_sheet As Worksheet
DeleteSheetWithoutWarning "items"
Set full_data_listobject = ThisWorkbook.Worksheets("full data").ListObjects(1)
' get the range currently selected
Set selected_range = Selection
selected_data_header = Selection.Offset(-1, 0).Cells(1, 1)
Set items_sheet = ThisWorkbook.Worksheets.Add
items_sheet.Name = "items"
selected_range.Copy
items_sheet.Range("A1").PasteSpecial xlValues
Set items_range = items_sheet.Range("A1").CurrentRegion
items_range.RemoveDuplicates 1, xlNo
'loop through each item and create a new sheet with the filtered data
For i = 1 To items_range.Rows.Count
If items_range.Cells(i, 1) <> "" Then
item = items_range.Cells(i, 1)
DeleteSheetWithoutWarning item
full_data_listobject.Range.Copy
Set new_item_sheet = ThisWorkbook.Worksheets.Add
With new_item_sheet
.Name = item
.Range("A1").Formula2 = "=" & full_data_listobject.Name & "[#Headers]"
.Range("A2").Formula2 = "=FILTER(" & full_data_listobject.Name & _
"," & full_data_listobject.Name & "[" & selected_data_header & "] = """ & item & """)"
''optionally uncomment these two lines to paste the results as values in the destination sheet
'.Range("A1").CurrentRegion.Copy
'.Range("A1").PasteSpecial xlPasteValues
End With
Else
Exit For
End If
Next i
End Sub
Private Sub DeleteSheetWithoutWarning(sheet_name As String)
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(sheet_name).Delete
On Error GoTo 0
Application.DisplayAlerts = True
End Sub
this is the current code i've gotten online