El número de pedido lo puedes calcular con la función MAX calculada sobre los pedidos que ya tengas creados y añadiéndole 1.

en el ejemplo al que te remití, se copiaba una fila entera y se pegaba tal cual en la hoja conjunta.

Si tu formulario tiene otro aspecto, tendrás que tomar los datos celda a celda e irlos colocando en su sitio al final de la lista conjunta.

Graba una macro que lo haga una vez y luego mira el código grabado para ver cómo lo ha hecho. después modifica la macro para que anote en la fila correcta.

Si quieres puedes adjuntar un archivo de ejemplo.

------
Ya sé Excel, pero necesito más

los archivos adjuntos aparecen al final del mensaje.

para añadir un adjunto, fíjate que aparece un conjunto de opciones bajo la zona del cuerpo del mensaje donde puedes añadir adjuntos. pulsa sobre el texto Archivos adjuntos para desplegarlo en caso de que no se muestre.

pulsa examinar para elegir el archivo de tu equipo, luego pulsa adjuntar para subirlo.
por último, envía el comentario para publicarlo.

------
Ya sé Excel, pero necesito más

Sólo los usuarios registrados pueden adjuntar archivos. Se pueden adjuntar archivos también en los comentarios.

Si contestas de manera anónima, no podrás adjuntar archivos. (aparte, los comentarios anónimos no se publican de inmediato, sino que pasan a la cola de aprobación y tengo que publicarlos manualmente después de haberlos leído, esto es para prevenir ataques de spam)

Inicia sesión antes de contestar. Puedes activar la opción recordarme para así recordar el inicio de sesión en tu equipo para que la próxima vez que entres en el sitio no tengas que volver a identificarte.

------
Ya sé Excel, pero necesito más

justo encima de los botones para enviar el comentario, ahí pone "Archivos adjuntos"

------
Ya sé Excel, pero necesito más

En primer lugar tienes una cabecera del pedido que debe repetirse para cada líena del consolidado

luego, en cada pedido tienes una serie de artículos identificados por la referencia

tu macro debe leer los datos comunes del encabezado, y luego debe tener un bucle que recorra todas las filas del pedido, anotando cada una de estas filas en una fila del consolidado.

Lo que tiene que hacer tu macro es ir asignando valores en las celdas del consolidado

Te sugiero que localices la celda donde empezar a escribir, y luego utilizar Offset a partir de esta celda para indicar las posiciones respecto de esa primera celda.

Luego es sólo entretenerse en ir identiificando posiciones.

Te adjunto un ejemplo con el siguiente código de ejemplo, debes completar los datos que faltan por emparejar

 Sub TransferirDatos()
' paco megía 2010.
' http://www.necesitomas.com/invitar
'

    Const NombreHojaPedido = "Pedido"
    Const NombreHojaConsolidado = "Consolidado"
   
    Dim CeldaLibreConsolidado As Range
    Dim Item
    Dim ContadorFilas   ' para saber las filas que se transfieren
   
    ' localizamos la celda libre
    With Worksheets(NombreHojaConsolidado)
        If IsEmpty(.Range("B3")) Then
            Set CeldaLibreConsolidado = .Range("B3")
        Else
            Set CeldaLibreConsolidado = .Range("B2").End(xlDown).Offset(1, 0)
        End If
    End With
   
   
    With Worksheets(NombreHojaPedido)
        'activamos la primera línea de artículos
        .Activate
        .Range("B12").Select    ' el campo Ref
        ContadorFilas = 0
        Do Until IsEmpty(ActiveCell)
        ' un bucle que se repita hasta que encuentre una fila con el campo "Ref" vacío
                       
            ' datos comunes
            CeldaLibreConsolidado.Offset(ContadorFilas, 1).Value = .Range("J6").Value ' nº pedido
            CeldaLibreConsolidado.Offset(ContadorFilas, 2).Value = .Range("B6").Value ' fecha
            '.....
            ' y así sucesivamente con el resto de tus datos comunes
           
           
            ' Ahora los datos de cada fila del artículo
            CeldaLibreConsolidado.Offset(ContadorFilas, 0).Value = ActiveCell.Offset(0, -1).Value ' Item
            CeldaLibreConsolidado.Offset(ContadorFilas, 8).Value = ActiveCell.Offset(0, 0).Value 'referencia
            CeldaLibreConsolidado.Offset(ContadorFilas, 9).Value = ActiveCell.Offset(0, 1).Value ' única XS
            '.....
            ' y así sucesivamente con el resto de tus datos
           

            ' pasamos a la siguiente fila del pedido
            ActiveCell.Offset(1, 0).Select
            ContadorFilas = ContadorFilas + 1
        Loop
       
        ' ahora que los datos están transferidos, puedes borrarlos de la hoja pedido,
            Range("B6").ClearContents
            Range("B7").ClearContents
            Range("B8").ClearContents
            Range("B9").ClearContents
            Range("F6").ClearContents
            Range("F7").ClearContents
            Range("F8").ClearContents
            Range("I8").ClearContents
            Range("B12:G51").ClearContents
           
        ' y asignar el número sigiente de pedido
        .Range("J6").Value = .Range("J6").Value + 1
       
        ' y dejarlo preparado en la celda que queramos, para el siguiente pedido
        .Range("B6").Select
    End With
   
    ' para terminar no viene mál poner un mensaje
    MsgBox "Transferidos " & ContadorFilas & " registros.", vbOKOnly, "necesitomas.com"
   
End Sub

Espero que con esto tengas pistas más que suficientes para hacer tu macro.

------
Ya sé Excel, pero necesito más

no lo hagas en Excel. Utiliza Word y combinar correspondencia para generar tu listado.

o eso, o programas una macro que te pregunte el número de pedido y lea todos los artículos del pedido y rellene la hoja que se imprimirá.

Otra opción es una consulta de datos externos que haga que el libro se consulte a sí mismo dentro de Excel.

Es que estás al límite entre una hoja de cálculo y una base de datos relacional, y para las bases relacionales Excel no es la herramienta.

------
Ya sé Excel, pero necesito más