VBA to Collapse Rows (3 part question)

[Kellie220]

Hi! I am looking for a VBA code that will hide/unhide (or) Collapse/Expand rows in a worksheet I am using. Column B is "ItemID". We have multiple ItemIDs with different Serial#'s. We would like to be able just to see ItemID once but have the ability to expand and see the multiple serial#'s attached. We do input the ItemId over and over to capture the different Serial#'s. The first entry we do is the main entry, that would be the one we would want to see. Everytime we enter an ItemID, that is the main entry, every other row is for more unique data in other columns that are associated with the "ItemID".
We would would prefer VBA over the filter collapse/expand). The view we typically use is the collapsed view, but we do have the need to see the expanded version. (This is the First question).

(Second question) Our workbook has 2 worksheets. We input info on worksheet one, then collapse the data (very old school method), then copy and paste it into worksheet 2 at the bottom. We would like to not have to always copy and paste the collapsed info. To have it just transfer over to Sheet 2 into the next available rows. We would like to have worksheet 1 drive worksheet 2. If changes are made on worksheet 1 then worksheet 2 changes. Also have whatever information transferred to worksheet 2 to not be editable. (You have to go to worksheet 1 to do edits on those columns). Our Sheet 1 just has our initial entry, only about 15 columns, where as sheet 2 is where we do the actual work and it has over 150 columns. We would like all of the information in the columns on Sheet 1 to be transfer into sheet 2 **If this could be based on column count just in case sometimes we add an additional column into sheet 1 we want it to then reflect into sheet 2.
(Last question) for both sheets to be able to automatically accept new entries without having to expand the table. Below is a very small example. Our current worksheet has over 30 thousand lines and 150 columns. I tried to condense since the ask is only formatting.

Any help would be appreciated.

Based on Column B "ItemID" - Below is what we would like (collapsed):

DEPT	ItemID	SERIAL	DUE Date	CAP/OM	PO	Priority	Tier Level	CreatedDate
84	RO2112206	SL-1691510E	9/30/2023	CAP	272593803	2	NonThreat	9/30/2022
84	RO2112714	SL-1690751E	11/7/2023	CAP	272686576	2	NonThreat	11/7/2022
84	RO2112737	SL-1688824E	11/16/2023	CAP	272706536	2	NonThreat	11/16/2022
84	RO2112870	SL-1693226E	9/1/2023	CAP	272537030	2	NonThreat	9/1/2022
70	RO2115078	SL-429189S	3/31/2023	CAP	272553711	2	NonThreat	9/11/2022
16	RO2115618	SL-1997834E	9/14/2022	CAP	270346857	2	NonThreat	9/14/2019
150	RO2115634	SL-45012S	8/20/2022	CAP	388818517	2	NonThreat	9/20/2017
87	RO2117038	UG-5174638	12/31/2023	CAP	272872764	2	NonThreat	3/1/2023
87	RO2117132	SL-59367S	12/30/2023	CAP	272905115	2	NonThreat	3/20/2023

Based on Column B "ItemID" - This is Expanded

DEPT	ItemID	SERIAL	DUE Date	CAP/OM	PO	Priority	Tier Level	CreatedDate
84	RO2112206	SL-1691510E	9/30/2023	CAP	272593803	2	NonThreat	9/30/2022
84	RO2112714	SL-1690751E	11/7/2023	CAP	272686576	2	NonThreat	11/7/2022
84	RO2112714	SL-1688781E	10/7/2023	CAP	272616479	2	NonThreat	10/7/2022
84	RO2112714	SL-1695334E	10/5/2023	CAP	272613005	2	NonThreat	10/6/2022
84	RO2112737	SL-1688824E	11/16/2023	CAP	272706536	2	NonThreat	11/16/2022
84	RO2112870	SL-1693226E	9/1/2023	CAP	272537030	2	NonThreat	9/1/2022
84	RO2112870	SL-1693317E	12/18/2023	CAP	272761397	2	NonThreat	12/17/2022
70	RO2115078	SL-429189S	3/31/2023	CAP	272553711	2	NonThreat	9/11/2022
16	RO2115618	SL-1997834E	9/14/2022	CAP	270346857	2	NonThreat	9/14/2019
150	RO2115634	SL-45012S	8/20/2022	CAP	388818517	2	NonThreat	9/20/2017
87	RO2117038	UG-5174638	12/31/2023	CAP	272872764	2	NonThreat	3/1/2023
70	RO2117038	SL-4373647E	12/31/2023	CAP	272620491	2	NonThreat	10/10/2022
150	RO2117038	SL-2292235E	8/20/2022	CAP	388818542	2	NonThreat	9/20/2017
87	RO2117132	SL-59367S	12/30/2023	CAP	272905115	2	NonThreat	3/20/2023
87	RO2117132	UG-5279208	12/31/2023	CAP	272872662	2	NonThreat	3/1/2023

 

[Artik]

Ad 1. Run the macro one time from the Macro window. Then click on the button.

Sub Toggle()
    Dim v As Variant
    Dim oDic As Object
    Dim oDicRows As Object
    Dim i As Long
    Dim lCnt As Long
    Dim shpToggle As Shape


    On Error Resume Next
    Set shpToggle = ActiveSheet.Shapes("tgl_Button")
    On Error GoTo 0

    If shpToggle Is Nothing Then
        Set shpToggle = AddButton
    End If

    If shpToggle.TextFrame2.TextRange.Text = "Collapse" Then
        shpToggle.TextFrame2.TextRange.Text = "Expand"

        Set oDic = CreateObject("Scripting.Dictionary")
        Set oDicRows = CreateObject("Scripting.Dictionary")

        With Range("A1").CurrentRegion
            v = .Offset(1).Resize(.Rows.Count - 1).Columns(2).Value
        End With


        On Error Resume Next

        For i = 1 To UBound(v)
            Err.Clear
            oDic.Add v(i, 1), Empty
            If Err.Number <> 0 Then
                oDicRows.Add i + 1, Empty
            End If
        Next i

        If UBound(oDicRows.Keys()) > -1 Then
            v = oDicRows.Keys()
            For i = 0 To UBound(v)
                Rows(v(i)).Hidden = True
            Next i
        End If
    Else
        shpToggle.TextFrame2.TextRange.Text = "Collapse"
        Rows.Hidden = False
    End If
End Sub


Private Function AddButton() As Shape
    Dim Shp As Shape

    Set Shp = ActiveSheet.Shapes.AddShape(msoShapeBevel, 154, 5, 92, 25)
    
    With Shp
        .TextFrame2.TextRange.Text = "Collapse"
        .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextFrame2.VerticalAnchor = msoAnchorMiddle
        .Name = "tgl_Button"
        .OnAction = "Toggle"
    End With

    Set AddButton = Shp
End Function

Artik