Automate Grouping of data in a report

[Cantrecallmyusername]

Hi there,

On my tab JIRA data I want the column with headers Project Status in Column N copied to the last row along with Column name Key in column A and B to a new Tab called Macro in to cells A6.

Then I need to sort the data by Project Status (A-Z) I will add formulas into cells C6-J6 - I want these dragged to the last row - initially I will use dummy formulas in this case - I can replace.

Every time the value in column A changes (project status) I want an insert of 3 lines. The first line is blank, on the second line in column B add the project status in to the cell and on the third line add in the following headers in the cells going from current cell to the right Item,
Description,
Last Update,
Start Date,
Project Phase,
Target Implementation,
Last Update Date,
RAG,
Project Status

Here is my input data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Key												Project Status
2	1	ABC-1												Active
3	2	ABC-2												Active
4	3	ABC-3												Active
5	4	ABC-4												Active
6	5	ABC-5												Active
7	6	ABC-6												Active
8	7	ABC-7												Active
9	8	ABC-8												Backlog
10	9	ABC-9												Backlog
11	10	ABC-10												Backlog
12	11	ABC-11												Backlog
13	12	ABC-12												Backlog
14	13	ABC-13												CA Driven
15	14	ABC-14												CA Driven
16	15	ABC-15												CA Driven
17	16	ABC-16												CA Driven
18	17	ABC-17												CA Driven
19	18	ABC-18												CA Driven
20	19	ABC-19												CA Driven
21	20	ABC-20												GPM Queue
22	21	ABC-21												GPM Queue
23	22	ABC-22												GPM Queue
24	23	ABC-23												GPM Queue
25	24	ABC-24												GPM Queue
26	25	ABC-25												New Change Request
27	26	ABC-26												New Change Request
28	27	ABC-27												New Change Request
29	28	ABC-28												New Change Request
30	29	ABC-29												Pipeline
31	30	ABC-30												Pipeline
32	31	ABC-31												Pipeline
33	32	ABC-32												On Hold
34	33	ABC-33												On Hold
35	34	ABC-34												On Hold
JIRA Data

This is my desired output

Book1
	A	B	C	D	E	F	G	H	I	J	K
4	Status	Active
5			Item	Des	Last Update	Start Date	Project Phase	Target Implementation	Last Update Date	RAG	Project Status
6	1	SEBP_1	10	20	30	40	50	60	17	18	1
7	2	SEBP_2	10	20	30	40	50	60	17	18	1
8	3	SEBP_3	10	20	30	40	50	60	17	18	1
9	4	SEBP_4	10	20	30	40	50	60	17	18	1
10	5	SEBP_5	10	20	30	40	50	60	17	18	1
11	6	SEBP_6	10	20	30	40	50	60	17	18	1
12	7	SEBP_7	10	20	30	40	50	60	17	18	1
13
14	Status	Backlog
15			Item	Des	Last Update	Start Date	Project Phase	Target Implementation	Last Update Date	RAG	Project Status
16	8	SEBP_8	10	20	30	40	50	60	17	18	1
17	9	SEBP_9	10	20	30	40	50	60	17	18	1
18	10	SEBP_10	10	20	30	40	50	60	17	18	1
19	11	SEBP_11	10	20	30	40	50	60	17	18	1
20	12	SEBP_12	10	20	30	40	50	60	17	18	1
21
22	Status	CA Driven
23			Item	Des	Last Update	Start Date	Project Phase	Target Implementation	Last Update Date	RAG	Project Status
24	13	SEBP_13	10	20	30	40	50	60	17	18	1
25	14	SEBP_14	10	20	30	40	50	60	17	18	1
26	15	SEBP_15	10	20	30	40	50	60	17	18	1
27	16	SEBP_16	10	20	30	40	50	60	17	18	1
28	17	SEBP_17	10	20	30	40	50	60	17	18	1
29	18	SEBP_18	10	20	30	40	50	60	17	18	1
30	19	SEBP_19	10	20	30	40	50	60	17	18	1
31
32
Macro

I have the below code that does some of this however the headers are off and not repeated in the sheet for me.

Sub CopyAndOrganizeData()
    Dim wsJIRA As Worksheet
    Dim wsMacro As Worksheet
    Dim lastRowJIRA As Long
    Dim lastRowMacro As Long
    Dim currentStatus As String
    Dim statusRow As Long
    Dim headerRow As Long
    Dim i As Long
    
    ' Set references to the worksheets
    Set wsJIRA = ThisWorkbook.Sheets("JIRA Data")
    Set wsMacro = ThisWorkbook.Sheets("Macro")
    
    ' Find the last row of data in JIRA Data sheet
    lastRowJIRA = wsJIRA.Cells(wsJIRA.Rows.Count, "B").End(xlUp).Row
    
    ' Find the last row of data in Macro sheet
    lastRowMacro = wsMacro.Cells(wsMacro.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Initialize statusRow
    statusRow = lastRowMacro
    
    ' Loop through the data and organize it by status
    For i = 1 To lastRowJIRA
        If wsJIRA.Cells(i, "N").Value <> currentStatus Then
            ' Add a header for the current status
            currentStatus = wsJIRA.Cells(i, "N").Value
            
            ' Add grouping header
            headerRow = lastRowMacro
            wsMacro.Cells(headerRow, 1).Value = "Status"
            wsMacro.Cells(headerRow, 2).Value = "Grouping"
            
            ' Add headers below the grouping header
            headerRow = headerRow + 1
            wsMacro.Cells(headerRow, 3).Value = "Item"
            wsMacro.Cells(headerRow, 4).Value = "Des"
            wsMacro.Cells(headerRow, 5).Value = "Last Update"
            wsMacro.Cells(headerRow, 6).Value = "Start Date"
            wsMacro.Cells(headerRow, 7).Value = "Project Phase"
            wsMacro.Cells(headerRow, 8).Value = "Target Implementation"
            wsMacro.Cells(headerRow, 9).Value = "Last Update Date"
            wsMacro.Cells(headerRow, 10).Value = "RAG"
            wsMacro.Cells(headerRow, 11).Value = "Project Status"
            
            ' Update statusRow for next status
            statusRow = headerRow + 1
            
            lastRowMacro = lastRowMacro + 1 ' Add line spacing
        End If
        
        ' Copy data to Macro sheet
        wsJIRA.Rows(i).Copy wsMacro.Rows(lastRowMacro)
        lastRowMacro = lastRowMacro + 1
    Next i
    
    ' Clean up
    Application.CutCopyMode = False
    Set wsJIRA = Nothing
    Set wsMacro = Nothing
End Sub

Help would be greatly appreciated with this one