Generar reporte de base de datos con información repetida

Sube una captura de pantalla de tu información me quedan algunas dudas por aclarar antes de darte una respuesta o pedirte el archivo

¿Esto es lo que buscas?, la macro esta abajo de la imagen, solo cambia el nombre de la hojas en los worksheets por el nombre de las hojas que manejes

Sub ANALIZAR_INFORMACION()
Set H1 = Worksheets("HOJA1")
Set h2 = Worksheets("HOJA2")
Set DATOS = H1.Range("B2").CurrentRegion
h2.Cells.Clear
With DATOS
    COLUMNA = H1.Range("G1").Column
    COLUMNAS = .Columns.Count
    DIFERENCIA = COLUMNAS - COLUMNA
    Set DATOS = .Rows(2).Resize(.Rows.Count - 1)
    Set DATOS2 = .Columns(COLUMNA).Resize(.Rows.Count, DIFERENCIA + 1)
    Set DATOS3 = .Resize(.Rows.Count, COLUMNA - 1)
    DIVIDIR = (DIFERENCIA + 1) / 3: FILAS = .Rows.Count
End With
With DATOS3
    For I = 1 To DIVIDIR
        If I = 1 Then
            Set DESTINO = h2.Range("B3").Resize(FILAS, COLUMNA - 1)
            Set PRODUCTO = .Columns(COLUMNA).Resize(FILAS, 3)
        End If
        If I > 1 Then
            Set DESTINO = DESTINO.Rows(FILAS + 1).Resize(FILAS, COLUMNA - 1)
            Set PRODUCTO = PRODUCTO.Columns(4).Resize(FILAS, 3)
        End If
        DESTINO.Value = .Value
        DESTINO.Columns(COLUMNA).Resize(FILAS, 3).Value = _
        PRODUCTO.Value
    Next I
End With
With DESTINO.CurrentRegion
    .Name = "PRODUCTOS"
    .Sort KEY1:=h2.Range(.Columns(7).Address), ORDER1:=xlAscending
    .Rows(0).Value = DATOS3.Rows(0).Value
    .EntireColumn.AutoFit
    .Cells(0, 7) = "Item"
    .Cells(0, 8) = "Cant"
    .Cells(0, 9) = "det"
End With
End Sub

Hola James Bond, buenas tardes:

Primero que todo muchas gracias por la celeridad con la que ha dado respuesta a mi consulta.

Lo que me envía se aproxima mucho a mi necesidad, creo que en el texto de la consulta no mencioné que el informe me debe quedar en un nuevo libro de trabajo, el código que me envía me organiza la información dentro de la misma hoja donde esta la base de datos, ¿sera posible generar el informe en un nuevo libro de trabajo?, además abusando de su gentileza, lo que quiero es poder generar el informe por un código de item determinado, se me ocurre utilizando un formulario donde pueda ingresar el código para el cual se desea generar el reporte, ¿sera esto posible?.

Nuevamente muchas gracias por el tiempo dedicado para resolver nuestras consultas.

Primero crea el siguiente formulario, es un combobox, un listbox y un botón comando y le pegas el código que viene abajo y en un modulo estándar pegas el primer código que puse en la primera respuesta, ambos códigos harán lo que pides.

´

Private Sub ComboBox1_Change()
valor = ComboBox1.Value
Set productos = Range("productos")
Set funcion = WorksheetFunction
With productos
    fila = funcion.Match(Val(valor), .Columns(7), 0)
    cuenta = funcion.CountIf(.Columns(7), valor)
    Set articulo = .Rows(fila).Resize(cuenta, .Columns.Count)
    .Columns(.Columns.Count + 3).CurrentRegion.Clear
    Set DESTINO = .Columns(.Columns.Count + 3).Resize(cuenta, .Columns.Count)
    DESTINO.Value = articulo.Value
    DESTINO.Rows(0).Value = productos.Rows(0).Value
    With ListBox1
        .ColumnCount = productos.Columns.Count
        .ColumnHeads = True
        .ColumnWidths = "80;180;40;40;40;40;40;60;60;"
        .RowSource = "=" & DESTINO.Address
    End With
    DESTINO.Name = "DESTINO"
End With
End Sub
Private Sub CommandButton1_Click()
Set DESTINO = Range("DESTINO").CurrentRegion
LIBRO = ActiveWorkbook.Name
Set nuevo = Workbooks.Add
With DESTINO
    Range("B2").Resize(.Rows.Count, .Columns.Count).Value = _
    DESTINO.Value
    Range("B2").CurrentRegion.EntireColumn.AutoFit
End With
Workbooks(LIBRO).Activate
End Sub
Private Sub UserForm_Initialize()
Dim UNICOS As New Collection
ANALIZAR_INFORMACION
Set productos = Range("PRODUCTOS")
With productos
    For i = 1 To .Rows.Count
        Item = .Cells(i, 7)
        On Error Resume Next
            UNICOS.Add Item, CStr(Item)
            If Err.Number = 0 Then ComboBox1.AddItem Item
        On Error GoTo 0
    Next i
    ComboBox1.ListIndex = 0
End With
Set producto = Nothing
End Sub

Hola James Bond buenas tardes:

Aun me queda un detalle que posiblemente no lo expliqué muy bn en la consulta, y en la revisión anterior no lo observé, y es que en la hoja Productos se encuentra la relación de todos los items, idependiente de que esten o no en la hoja que contiene la base de datos, la idea es que el combobox pueda leer ya sea el código del item que esta en la columna A, o la descripción que esta en la columna B, seria maravilloso si en el combo se pudiran mostrar los dos datos concatenados, pero que la busqueda la realice por el código.

Actualmente me esta reemplazando la información de la hoja productos con la información de la hoja Relacion_Ordenes, la idea es que la información de ninguna de las hojas del archivo sea modificada, esta es la razón por la cual el informe debe ser generado en un libro nuevo, esta parte ya la hace muy bien.

Nuevamente agradezco su valioa colaboración.

adjunto imagen del la hoja Productos.