Using VBA to consolidate mult spreadsheets

[marissae]

Hello- A friend of mine wrote a VBA/macro (not sure what the difference is) in Excel for me to move data from 2002-2015. I couldn't just consolidate the spreadsheets because the rows and columns were all different, so I coded them. He wrote the program to find the month/year, then the X variable, then the Y variable, then position the data point. My issue is that I now need to expand and include 1998-2001. I've pasted the macro below. Please let me know if you have suggestions. The macro is in Spanish, I can translate any unknowns. He had some comments that he listed in green on the macro, but it all shows up below. Thank you!

Sub DATO_MESANIO_BANCO_CUENTA()
'
' DATO_MESANIO_BANCO_CUENTA Macro
' CONTADOR DE CARPETA-ARCHIVO
' CONTADOR DE MES-AÑO
' CONTADOR DE BANCO
' CONTADOR DE CUENTA
'
'Application.ScreenUpdating = False

Dim Cont, Ref, OC, Nomb, Nom, Num, Doc, Bod, Trans, Cli, Codi, RUC As String
Dim I, J, K, L, ENE, INE, Cant As Integer
Dim Fecha As Date
Dim RucCed As Boolean
Estilo = vbYesNo + vbCritical + vbDefaultButton2


' CONTADOR DE ARCHIVO:
MA = 17 ' Este índice se modifica a 1 para ejecutar todos los años
Do While MA <= 16
If MA < 10 Then AAAA = "200" & MA Else AAAA = "20" & MA

Select Case MA
Case 1
Nomx = "Early 2002.xlsx"
Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
Nomx = AAAA & ".xlsx"
Case 15
Nomx = "Early 2015.xlsx"
Case 16
Nomx = "Late 2015.xlsx"
End Select


Nomb = "F:\\MARISSA\" & Nomx
'Nomb = "C:\Este equipo\Documentos\GAME\MARISSA\" & Nomx
'Nomb = "P:\GAME\MARISSA\" & Nomx
Workbooks.Open (Nomb)
'If Err.Number = 1004 Then
'Err.Clear
'MsgBox Prompt:="Se intentó abrir el archivo " & Nomx & " que no está en la carpeta P:\GAME\MARISSA!"
'Exit Sub
'End If

Sheets("Sheet1").Select

' CONTADOR DE MES-AÑO:
I = 1
I2 = 1
Do
Fecha = Cells(I, 1)
Anio = Year(Fecha)
Mes = Month(Fecha)
IMA = (Anio - 2002) * 12 + Mes
If Cells(I, 1) <> "" Then GoTo Procesa
I2 = I2 + 1
If I2 = 100 Then
Exit Do ' Salir contador mes-año
Else
I = I + 1
GoTo FinLoopMesA
End If

Procesa:
Cells(1, 2) = Fecha
J = Cells(1, 3) ' Fila inicial de bloque

' CONTADOR PARA BLOQUE MES-AÑO SIGUIENTE:
K = J + 1
I2K = 1
Do
FechaK = Cells(K, 1)
If Cells(K, 1) <> "" Then GoTo ProcesaK
I2K = I2K + 1
If I2K = 100 Then
Exit Do ' Salir contador mes-año siguiente
Else
K = K + 1
GoTo FinLoop
End If

ProcesaK:
Cells(1, 2) = FechaK
L = Cells(1, 3) ' Fila siguiente de bloque
Exit Do
FinLoop:
Loop

L = L - 1 ' Fila final de bloque

' CONTADOR DE CUENTA EN BLOQUE:
M = J + 3
Do While M <= L

Worksheets("Sheet1").Cells(M, 4).Select
R = Cells(M, 4) ' N° Cuenta

' CONTADOR DE BANCO:
N = 5
Do While Cells(J + 2, N) <> ""
P = Cells(J + 2, N) ' N° Banco
Worksheets("Sheet1").Cells(M, N).Select
Valor = Cells(M, N) ' Dato

' BÚSQUEDA DE MES-AÑO, BANCO Y CUENTA:
Windows("Masterm.xlsm").Activate
Sheets("Sheet1").Select
' Fila 2: Banco: P
' Fila 3: Cuenta: R
' Columna 1: N° de mes-año: IMA

'Worksheets("Sheet1").Cells(200, 1).Select

Cells(200, 1) = IMA ' N° de mes-año
S = Cells(201, 1) ' Fila de N° de mes-año
Cells(202, 1) = P ' N° banco
Cells(204, 1) = R ' N° cuenta
T = Cells(205, 1) ' Columna de cuenta-banco
Worksheets("Sheet1").Cells(S, T).Select
Cells(S, T) = Valor
' Activar Archivo año:
Windows(Nomx).Activate
Sheets("Sheet1").Select


N = N + 1
Loop ' Contador banco

M = M + 1
'Loop ' Contador cuenta

Loop ' Contador de cuenta en bloque

I = K

FinLoopMesA:
Loop ' FIN CONTADOR MES AÑO



'Respu = MsgBox("Se terminó año "& Nomx & ": Continuar? [Sí/No]", Estilo)
'If Respu = vbNo Then Exit Sub
MA = MA + 1
Workbooks(Nomx).Close (SaveChanges = False) 'Cerrar archivo
Loop ' FIN CONTADOR ARCHIVO

Windows("Masterm.xlsm").Activate
Sheets("Sheet1").Select
Cells(2, 1) = Time




'Application.ScreenUpdating = True
'Application.CutCopyMode = False



End Sub

[alansidman]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)