Excel 2007 : Import data from a worksheet into another and group data into sections

[scottmj40]

I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix
The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value
The Data that i want to export will be from the first 5 columns within the Generated REport(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.

BRID Requirement Bug Description BUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj 43712 123
PQ116 PQ116-Landing Bug Description text ghghghgoot 43713 126


For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.



These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath


BRID Priority Requirment Description BugID Alt Bug ID PC Update Notes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj 43712 123 To Do

BRID Priority Requirment Description BugID Alt Bug ID QC Update Notes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot 43713 126 To Do


So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corrresponding BRID section
within the Matrix. I have to repeate this step for each new BRID and i want to be able to wholle automate this process

Dim Buglist_Trial As Workbook
    Dim Release_Notes As Workbook
     
    Dim rngSourceRange As Range
    Dim rngDestination As Range
     
    Set Buglist_Trial = ActiveWorkbook
     
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2002-03", "*.xls", 1
        .Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        .AllowMultiSelect = False
        .Show
         
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set Generated Report = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)
            Buglist_Trial.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            Release_Notes.Close False
        End If
    End With