Excel - error de precedimiento demasiado largo

Hola,
Estoy empezando a programar en VB y estoy haciendo un control de faltas de alumnos. 7 ciclos a 35 alumnos por ciclo, 9 meses, 31 dias por mes.... Bueno querria estructurarlo en tablas para poder tratar los datos posteriormente, es decir querria acumular datos en tablas provenientes de un formulario tabla. Pero es codigo es demasidado extenso y ya no se por donde cogerlo. Os envio una parte del codigo, (un mes).
Muchas gracias de antemano.

Sub pasardatos()
Dim e As Long, f As Long
Dim c As Long, d As Long
Dim i As Long, j As Long

Dim MiMatriz(1 To 35, 1 To 6)


'--------------------------------------------------------SETEMBRE-------------------------------------------------------------------------


If Range("C1").Text = "SETEMBRE" Then 'si es SETEMBRE guardara les dades als dies de setembre.



If Range("G1").Value = "1" Then 'si es dia 1 guardara les dades a dia 1.
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 1

If Range("G1").Value = "2" Then 'si es dia 2 guardara les dades a dia 2.
c = 3
d = 1
e = 43
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 2

If Range("G1").Value = "3" Then 'si es dia 1 guardara les dades a dia 3.
c = 3
d = 1
e = 83
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 3

If Range("G1").Value = "4" Then 'si es dia 4 guardara les dades a dia 4.
c = 3
d = 1
e = 123
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 4

If Range("G1").Value = "5" Then 'si es dia 1 guardara les dades a dia 5.
c = 3
d = 1
e = 163
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 5

If Range("G1").Value = "6" Then 'si es dia 1 guardara les dades a dia 6.
c = 3
d = 1
e = 203
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 6

If Range("G1").Value = "7" Then 'si es dia 1 guardara les dades a dia 7.
c = 3
d = 1
e = 243
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 7

If Range("G1").Value = "8" Then 'si es dia 1 guardara les dades a dia 8.
c = 3
d = 1
e = 283
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 8

If Range("G1").Value = "9" Then 'si es dia 1 guardara les dades a dia 9.
c = 3
d = 1
e = 323
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 9

If Range("G1").Value = "10" Then 'si es dia 1 guardara les dades a dia 10.
c = 3
d = 1
e = 363
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 10

If Range("G1").Value = "11" Then 'si es dia 1 guardara les dades a dia 11.
c = 3
d = 1
e = 403
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 11

If Range("G1").Value = "12" Then 'si es dia 1 guardara les dades a dia 12.
c = 3
d = 1
e = 443
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 12

If Range("G1").Value = "13" Then 'si es dia 1 guardara les dades a dia 13.
c = 3
d = 1
e = 483
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 13

If Range("G1").Value = "14" Then 'si es dia 1 guardara les dades a dia 14.
c = 3
d = 1
e = 523
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 13

If Range("G1").Value = "15" Then 'si es dia 1 guardara les dades a dia 15.
c = 3
d = 1
e = 563
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 15

If Range("G1").Value = "16" Then 'si es dia 1 guardara les dades a dia 16.
c = 3
d = 1
e = 603
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 16

If Range("G1").Value = "17" Then 'si es dia 1 guardara les dades a dia 17.
c = 3
d = 1
e = 643
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 17

If Range("G1").Value = "18" Then 'si es dia 1 guardara les dades a dia 18.
c = 3
d = 1
e = 683
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 18

If Range("G1").Value = "19" Then 'si es dia 1 guardara les dades a dia 19.
c = 3
d = 1
e = 723
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 19

If Range("G1").Value = "20" Then 'si es dia 1 guardara les dades a dia 20.
c = 3
d = 1
e = 763
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 20

If Range("G1").Value = "21" Then 'si es dia 1 guardara les dades a dia 21.
c = 3
d = 1
e = 803
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 21

If Range("G1").Value = "22" Then 'si es dia 1 guardara les dades a dia 22.
c = 3
d = 1
e = 843
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 22

If Range("G1").Value = "23" Then 'si es dia 1 guardara les dades a dia 23.
c = 3
d = 1
e = 883
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 23

If Range("G1").Value = "24" Then 'si es dia 1 guardara les dades a dia 24.
c = 3
d = 1
e = 923
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 24

If Range("G1").Value = "25" Then 'si es dia 1 guardara les dades a dia 25.
c = 3
d = 1
e = 963
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 25

If Range("G1").Value = "26" Then 'si es dia 1 guardara les dades a dia 26.
c = 3
d = 1
e = 1003
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 26

If Range("G1").Value = "27" Then 'si es dia 1 guardara les dades a dia 27.
c = 3
d = 1
e = 1043
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 27

If Range("G1").Value = "28" Then 'si es dia 1 guardara les dades a dia 28.
c = 3
d = 1
e = 1083
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 28

If Range("G1").Value = "29" Then 'si es dia 1 guardara les dades a dia 29.
c = 3
d = 1
e = 1123
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 29

If Range("G1").Value = "30" Then 'si es dia 1 guardara les dades a dia 30.
c = 3
d = 1
e = 1163
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 30

If Range("G1").Value = "31" Then 'si es dia 1 guardara les dades a dia 31.
c = 3
d = 1
e = 1203
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select
End If 'fi instruccio dia 31

End If 'fi instruccio setembre

ni idea de lo que pretendés hacer, pero seguramente puede simplificarse el código...

Saludos, desde Baires, JuanC

Hola Juan,
Gracias por tu atención.
Saludos.
A. Torné.

Este trozo de codigo se podria simplificar asi:

Muy buenas.

Buena solución. Hago un par de puntualizaciones:
Una, no creo que haga falta hacer el bucle for i = 1 to 31, bastará únicamente con coger el dato. Otra, el valor de "e", según está escrito coge un dato superior en 40 al que corresponde.
Yo, partiendo de la solución de Antoni, lo dejaría así:

Sub pasardatos()
Dim e As Long, f As Long, dia as integer
Dim c As Long, d As Long
Dim i As Long, j As Long, MiMatriz(1 To 35, 1 To 6)

'------ SETEMBRE -----

If Range("C1").Text = "SETEMBRE" Then 'si es SETEMBRE guardara les dades als dies de setembre.
c = 3
d = 1
e = val(Range("G1").Value)*40-37
f = 1
For i = 1 To 35
For j = 1 To 6
MiMatriz(i, j) = Worksheets("Hoja1").Cells(c + i, j + d).Value
Worksheets("Hoja2").Cells(e + i, j + f).Value = Worksheets("Hoja1").Cells(c + i, j + d).Value + Worksheets("Hoja2").Cells(e + i, j + f).Value
Next j
Next i
Range("B4:G38").ClearContents
Range("A1").Select

endif
end sub

Un saludo. Juanjo.

Hola de nuevo Jcs,
Estoy siguiendo con mi control de faltas y tengo un par de problemas. El primero es anidar los 9 meses de que consta el curso. Y segundo la lentitud en que copia los datos de la matriz de una hoja a la otra, podria ir más ràpido? Os envio el codigo de un par de meses. Como podreis apreciar se mueve 8 celdas hacia la derecha cada mes " f= 8"

Sub pasardatos()
Dim e As Variant, f As Variant
Dim c As Variant, d As Variant
Dim i As Variant, j As Variant, MiMatriz(1 To 35, 1 To 7)
Dim advertencia As String

'------ SETEMBRE -----
If Range("H4").Text = "CFGS P3 MATI" Then


If Range("C4").Text = "SETEMBRE" Then 'si es SETEMBRE guardara les dades als dies de SETEMBRE.
c = 6
d = 2
e = Val(Range("E4").Value) * 40 - 37
f = 0
For i = 1 To 35
For j = 1 To 7
MiMatriz(i, j) = Worksheets("FALTES").Cells(c + i, j + d).Value
Worksheets("P3M").Cells(e + i, j + f).Value = Worksheets("FALTES").Cells(c + i, j + d).Value
Next j
Next i
Range("D7:I41").ClearContents
Range("H4").Select
End If 'fi de la instruccio de setembre.

'-------OCTUBRE-------

If Range("C4").Text = "OCTUBRE" Then 'si es OCTUBRE guardara les dades als dies de OCTUBRE.
c = 6
d = 2
e = Val(Range("E4").Value) * 40 - 37
f = 8
For i = 1 To 35
For j = 1 To 7
MiMatriz(i, j) = Worksheets("FALTES").Cells(c + i, j + d).Value
Worksheets("P3M").Cells(e + i, j + f).Value = Worksheets("FALTES").Cells(c + i, j + d).Value
Next j
Next i
Range("D7:I41").ClearContents
Range("H4").Select

End If 'fi de la instruccio de OCTUBRE.
Muchas gracias.
Saludos.

Hola jcs,
Estoy sorprendido de como has simplicado mi codigo repetitivo, no sabia como hacerlo, de verdad, llevo muchos dias dandole vueltas. Voy a probar si me cubre para 9 meses.
Muchas gracias por tu ayuda.
Saludos.
A. Torné.

Hola Antoni,
Gracias por la simplificación del código, soys unos cracks!!
Voy a probar las dos opciones a ver si me cubre para los nueve meses. En cuanto a los diferentes cursos, agrgaré un boton para cada uno y cambiaré el nombre de las hojas.
Saludos.
A. Torné.

Hola de nou Antoni,
No he podido resistirme a probar tu nuevo código. Funciona perfectamente!!
Solo le he hecho un pequño retoque: en la insturcción " e = 3 + (dia * 40)" me colocaba los valores en el dia 2, ya que cada vez que cambiamos de tabla incrementamos en 40. Lo he solucionado restandole 40 y quedo asi: " e = 3 + (dia * 40 - 40)". Ahora al multiplicar "dia" por 1 situa el 3+1, al multiplicar "dia" por 2 lo situa 40 más abajo y asi sucesivamente.... Perfecto.
Muchas gracias.
Saludos.
A. Torné.