Copy rows into sheet according to entries in Column

Like this? I added a helper column to your data that uses the legend of codes you had on each sheet. All you need to do is extend the list and include the helper column so the Sheets know which rows to pull. We're pulling the unique ID called REF over with the heavy-lifting array, then using simple INDEX/MATCH to bring over the matching data for the rows that go with the Ref #.

The colored column on the individual sheet is the heavy formula. It's an array, so any edits should be confirmed with CTRL-SHIFT-ENTER before you copy it down. I've already structured the formula to analyze 1000 rows of data on Sheet1.

The Legend of Keys can go down to 100 codes without having to change the Key column formula.

Originally Posted by Hblbs

Hi, thank you both for these. The first solution works fine and can be tailored to report length but I would like to include this in my existing macro. The second also work very well, all the data is picked up despite varying report length.

The only thing is the dates seem to alter when their copied to the other sheets, some cells go from dd/mm/yyyy to mm/dd/yyyy.

Also don't know if I need to start a separate thread for this but since this needs to printed out I'm trying to get all borders applied so that it is clear to read and comments can be placed in Column O.

Many thanks

My sheet is just drawing the values over from the other sheet. Format those date columns (Ctrl-1) to display the dates any way you want.

=========
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Hi thanks for you replies. The code works fine on its own when the sheets are created but when I try to attach it to a user form that I use to open, format and save the spread sheet it does not work.

Error message :- run time error 9 : subscript out of range.

It only seems to work when I save, close and then re-open and run the code separately. When the code is run, the dates change to mm/dd/yyyy, but even when I go to format cell it stays as it is.

Private Sub cbReport_Click()
Private Sub cbReport_Click()
    Dim day As String
    Dim month As String
    Dim year As String
    Dim month_name As String
    Dim year_long As String
    Dim storage_path As String
    Dim generic_file_name As String
    Dim file_name As String
    Dim report_length As Integer
    Dim line_number As Integer
    
    'Assign text box values to respective variables
    day = tbDay
    month = tbMonth
    year = tbYear
    
    'Close Report window and set text box values to null
    Report.Hide
    tbDay = ""
    tbMonth = ""
    tbYear = ""
        
    'Set path for report storage area
    storage_path = Range("Path!A1")
    'Determine generic file name
    generic_file_name = Range("Path!A2")
      
    'Determine report file name from day, month and year
    'Determine month name
    Select Case month
        Case "01"
            month_name = "January"
        Case "02"
            month_name = "February"
        Case "03"
            month_name = "March"
        Case "04"
            month_name = "April"
        Case "05"
            month_name = "May"
        Case "06"
            month_name = "June"
        Case "07"
            month_name = "July"
        Case "08"
            month_name = "August"
        Case "09"
            month_name = "September"
        Case "10"
            month_name = "October"
        Case "11"
            month_name = "November"
        Case "12"
            month_name = "December"
    End Select
    
    'Determine year in 4 digit format
    year_long = "20" & year & ""

    'Store file name in variable file_name
    file_name = "" & generic_file_name & " " & day & " " & month & " " & year & ".xls"
       
    'Open report file
    Workbooks.Open Filename:="" & storage_path & "\" & year_long & "\" & month_name & "\" & file_name & ""
            
    'Procedure to format report with:
    
    Call AddsheetstoPrint
    
    Call x
       
exit_point:
    'Save report file
    ActiveWorkbook.Save
    'Close report file
    ActiveWorkbook.Close
    'Return to Main worksheet on macro program file
    Sheets("Main").Select
    'Display confirmation message box
    MsgBox "Report completed, stored in file:" & Chr(13) & storage_path & "\" & year_long & "\" & month_name & "\" & file_name & "", , ""
End Sub
    
End Sub

The Call Addsheets to print formats the sheets for printing and Call x copies the rows into the corresponding sheets.

Was there something wrong with the formula I gave you? What's wrong?