El truco está en detectar dónde (en qué fila) tienes el último dato en tu hoja consolidado para continuar anotando datos en la fila siguiente.

Tienes varias opciones para identificar esa última fila, te comento unas cuantas:

UsedRange es una propiedad de la hoja que indica el rango utilizado. Por ejemplo

filas_utilizadas=sheets("bdconsolidado").usedrange.rows.count  

una vez conocidas las filas, puedes emplearlas por ejemplo con el método Offset o con Range o Cells para situarte en la celda donde quieres empezar a anotar.

La pega de UsedRange es que puede no estar actualizado, así que si tuviste datos en la hoja y luego los borraste, puede que usedrange guarde el rango máximo que se utilizó, con lo que no sería válido.

un bucle: puedes realizar un bucle por las celdas hasta que llegues a una fila vacía

por ejemplo, suponiendo que todos tus datos tengan algo escrito en la columna A, podrías recorrerla del encabezado de la tabla hacia abajo hasta encontrar la primera celda vacía

Const primeracelda = "A3"   'la celda del encabezado, empezamos a mirar a partir de ahí

Const hojaconsolidado = "bdconsolidado"

Dim celdaLibre As Range

Dim contador As Long

contador = 0

With Sheets(hojaconsolidado)

    contador = 0

    Do Until IsEmpty(.Range(primeracelda).Offset(contador, 0))

        contador = contador + 1

    Loop

    Set celdaLibre = .Range(primeracelda).Offset(contador, 0) 'aquí tenemos la celda libre

End With

End(XlDown). esto es un método que tienen los rangos y se parece a como lo harías con el teclado con [Fin]+ [Fecha de dirección]

Te puedes colocar en la primera celda de la tabla, en este caso la A3, y luego ir a la última celda hacia abajo, como si pulsases [fin]+[flecha abajo], con esto irías a la última celda con datos en la columna A.

Sólo tienes que tener la precaución de que la primera vez, no tienes datos aún, así que habría que comprobar si la primera celda ya es vacía, para no saltar hasta la fila un millón.

podría ser algo así:

Const primeracelda = "A3"   'la celda del encabezado, empezamos a mirar a partir de ahí

Const hojaconsolidado = "bdconsolidado"

Dim celdaLibre As Range

With Sheets(hojaconsolidado)

    If IsEmpty(.Range(primeracelda).Offset(1, 0)) Then

        Set celdaLibre = .Range(primeracelda).Offset(1, 0)

    Else

        Set celdaLibre = .Range(primeracelda).End(xlDown).Offset(1, 0)

    End If

End With

Puede hacerse de varios maneras, dependerá de cómo sean tus datos, cómo sea la hoja de destino, ...