Una forma de lograr llenar una hoja con la sumatoria de celdas específicas en forma de consulta

¿Es esto lo que buscas?, ¿Por ejemplo seleccionar de la semana 2 a la 6 y sacar la sumatoria?

la cual queda asi en la hoja 2

si tu respuesta es si, esta es la macro que ocupas

Sub sumar()
Set DATOS = Range("b2").CurrentRegion
Set H2 = Worksheets("HOJA2")

With DATOS

Set DATOS = .Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1)
.Select
INICIO = Val(InputBox("SEMANA INICIAL", "AVISO"))
Final = Val(InputBox("SEMANA FINAL", "AVISO"))
Set AREA = .Columns(INICIO).Resize(.Rows.Count, Final - INICIO + 1)
SUMA = WorksheetFunction.Sum(AREA)
End With

With H2
.Range("B2") = "SEMANA " & INICIO
.Range("C2") = "SEMANA " & Final
.Range("D2") = SUMA
End With

End Sub

Los únicos datos que necesitas cambiar son los que están en los range() por los que tu estés usando en tu información.

Esto es increíble, es un grandísimo avance para mi y corre excelente solo, me preguntaba si había manera de poner que lance la sumatoria de una sola fila y se que modo?  

Me explico, de una sola parte, como en tu ejemplo de tyu-01 de la semana 1 a la 2.

En la siguiente imagen te muestro el resultado de la nueva macro, ahora la macro te pide un numero de parte, una semana de inicio y una de fin y te hace la selección en base a estos 3 parámetros.

luego en la hoja 2 va acumulando las consultas asi

y esta es la macro 

Sub sumar()
Set DATOS = Range("B2").CurrentRegion
Set H2 = Worksheets("HOJA2")
With DATOS
    columnas = .Columns.Count
    NP = InputBox("INGRESA NUMERO DE PARTE", "AVISO")
    If NP = Empty Then End
    fila = WorksheetFunction.Match(NP, .Columns(1), 0)
    Set partes = .Rows(fila).Resize(1, columnas)
    With partes
        Set partes = .Columns(2).Resize(1, columnas - 1)
        INICIO = Val(InputBox("INGRESA SEMANA INICIAL", "AVISO"))
        Final = Val(InputBox("INGRESA SEMANA FINAL", "AVISO"))
        If INICIO = Empty Or Final = Empty Then End
        Set AREA = .Columns(INICIO).Resize(.Rows.Count, Final - INICIO + 1)
        AREA.Select
        SUMA = WorksheetFunction.Sum(AREA)
    End With
End With
With H2
    Set DESTINO = .Range("B2").CurrentRegion
    With DESTINO
        FILAS = .Rows.Count: COLUMNASD = .Columns.Count
        If FILAS = 1 And COLUMNASD = 1 Then
            Set DESTINO = .Resize(1, 4)
        Else
            Set DESTINO = .Rows(FILAS + 1).Resize(1, 4)
        End If
        .Cells(1, 1) = NP:        .Cells(1, 2) = INICIO
        .Cells(1, 3) = Final:     .Cells(1, 4) = SUMA
    End With
End With
End Sub

recuerda no olvidar evaluar mi respuesta

De nuevo cambia solo tienes que cambiar el range("b2") y la hoja2 por el rango y la hoja que estés usando, las líneas terminadas en .select las puedes borrar, solo son para resaltar las líneas seleccionadas.

Esto facilito meses de trabajo y es perfecto y exactamente lo que buscaba mi cabeza voló con esto, muchísimas gracias! Esperaría que hubiera una forma de retribuirle 

Solo encontré una desventaja, sobre la macro, hay más de un número de parte idéntico en la lista , puesto que se usa la misma en distintos productos me explico, la lista es

tyc-001 taquete de puerta

Tyc-001taquete de ventana

Tyc-002 tornillo 

Tyc-002 mismo tornillo en ventana

Tyv-002 tornillo en puerta

Con esta macro me calcula solo la primera,

Sé que es abusar de el favor más grande pero hay manera de que sumen todos del mismo tambien si no, no hay problema mi calificación sigue 10/10 y estrellita para usted.