creo que la ultima pregunta

Ahora tengo otro error cuando pongo pongo esta formula me dice error

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,
Range("f18,f19,f20,f21,f22,f23,f24,f25,f26,f27,f28,f29,f30,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,e18,e19,e20,e21,e22,e23,e24,e25,e26,e27,e28,e29,e30,g18,g19,g20,g21,g22,g23,g24,g25,g26,g27,g28,g29,g30")) Is Nothing Then Exit Sub
Dim Hoja As Worksheet
Select Case Target.Address
Case "$F$18"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 2
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$19"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 3
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$20"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 4
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$21"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 5
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$22"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 6
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$23"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 7
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$24"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 8
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$25"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 9
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$26"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 10
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$27"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 11
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$28"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 12
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$29"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 13
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$F$30"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 14
Target.Copy Hoja.Range("h6,h23,h40")
End Select
Next
Case "$C$18"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 2
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$19"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 3
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$20"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 4
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$21"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 5
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$22"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 6
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$23"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 7
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$24"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 8
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$25"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 9
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$26"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 10
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$27"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 11
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$28"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 12
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$29"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 13
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$C$30"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 14
Target.Copy Hoja.Range("f7,f24,f41")
End Select
Next
Case "$D$18"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 2
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$19"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 3
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$20"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 4
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$21"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 5
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$22"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 6
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$23"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 7
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$24"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 8
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$25"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 9
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$26"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 10
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$27"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 11
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$28"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 12
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$29"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 13
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$D$30"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 14
Target.Copy Hoja.Range("i7,i24,i41")
End Select
Next
Case "$E$18"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 2
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$19"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 3
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$20"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 4
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$21"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 5
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$22"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 6
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$23"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 7
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$24"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 8
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$25"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 9
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$26"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 10
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$27"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 11
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$28"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 12
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$29"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 13
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$E$30"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 14
Target.Copy Hoja.Range("l7,l24,l41")
End Select
Next
Case "$G$18"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 2
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$19"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 3
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$20"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 4
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$21"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 5
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$22"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 6
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$23"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 7
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$24"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 8
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$25"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 9
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$26"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 10
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$27"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 11
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$28"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 12
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$29"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 13
Target.Copy Hoja.Range("k9,k26")
End Select
Next
Case "$G$30"
For Each Hoja In Worksheets
Select Case Hoja.Index
Case 14
Target.Copy Hoja.Range("k9,k26")
End Select
Next
End Select
End Sub

es un poco chiquita la formula como te dije... :P pero bue.. y todavia no
voy ni la mitad :( pero bue... decime como hago para que se aga accesible esa
formula...

me dice que "Se ha producido el error ´1004´ en tiempo de ejecucion
Error en el metodo ´range´ de objeto ´_Worksheet´
me dice me pone amarillo esta parte
If Intersect(Target,
Range("f18,f19,f20,f21,f22,f23,f24,f25,f26,f27,f28,f29,f30,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,e18,e19,e20,e21,e22,e23,e24,e25,e26,e27,e28,e29,e30,g18,g19,g20,g21,g22,g23,g24,g25,g26,g27,g28,g29,g30")) Is Nothing Then Exit Sub

y la otra duda que tengo es que no entendi mucho.. lo que entendi es que vos
queres que copie la formula... pero si quiero copiar el resultado de una
formula que esta en hoja 1 en hoja 7 en varios lugares de la hoja 7, como
hago... supongamos que =SUMA(J18:J30) y este en J31 de la hoja 1, como hago
para copiarlo en hoja 4 E3 y E7, el resultado pero que cambie a medida que
cambie J31??, si me pones la formula te lo agradeceria infinitamente, siempre
que sea compatible con la primera formula que expuse, porque seria la
continuacion de esa formula...