Ayuda sobre el uso de funciones para cargar valores de celdas utilizando Microsoft Excel

En realidad no es una consulta sencilla, pero aquí vamos.
Asumo que lo que quieres es una especie de máscara de entrada, de forma tal que al ingresar los datos en ella se transfieran a la segunda hoja *agregando las líneas una después de la otra*.
Usaremos el evento Change, como sugerías, controlando que la celda final sea D2 (Ultima a celda a cargar de la fila, pero puedes cambiarlo si esa no fuera la celda final)
Para que funcione, activa el editor de Visual Basic (presiona Alt+F11) y busca la hoja donde quieres que esto ocurra (donde harás la carga de datos). Da doble click sobre ella.
Copia el código siguiente y pégalo en el panel desplegado a la derecha de su Editor de Visual Basic:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "D2" Then FormCarga
End Sub
Este código controla que estuvieras en la celda D2 y, si así fuera, dispara la macro FormCarga.
Inserta un módulo nuevo y allí pegarás el código siguiente:
Sub FormCarga()
OrigSheet = ActiveSheet.Name
Range("A2:D2").Copy
'================== Modificar de acuerdo a tus datos reales
HojaDest = "Hoja2"
Firstcell = "A1"
'==================
Sheets(HojaDest).Select
Range(Firstcell).Select
LCol = Selection.Column
LCell = Selection.Row
LCell = LCell + Selection.CurrentRegion.Rows.Count
Cells(LCell, LCol).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells(LCell, LCol).Select
Sheets(OrigSheet).Select
Range("A2:D2").ClearContents
Range("A2").Select
End Sub
(Hay dos variables entre las líneas de ==== que deberás reemplazar por tus datos propios. Corresponden a la hoja donde se deben volcar los datos y la primer celda superior de la derecha donde comienza tu tabla)
Esta macro sirve para identificar la última celda ocupada en la hoja de carga y allí pega los datos cargados, luego, borra la hoja original para una nueva carga.
Cierra el editor de Visual Basic y graba el archivo.
Pruébalo y dime si funciona OK.
Espero que esto ayude a resolver tu problema. Si así fuera, agradeceré un comentario y que la finalices.
(Recuerda que mantener cierto número de respuestas pendientes impide que otros usuarios puedan consultarme)
En caso contrario, puedes preguntarme nuevamente
aclarando qué entendí mal o qué faltó.

Disculpa mi insistencia, pero no he podido resolver mi problema, te agradecería mucho que me dieras tu mail para enviarte mi archivo y que lo analices, si me pudieras apoyar, te lo agradecería muchísimo. Saludos.

Es extraño, el código es un procedimiento probado varias veces. Tal vez deberías revisar las direcciones de celdas que se incluyen en él para que coincidan con las tuyas propias.
De todos modos, si quieres mandarme el archivo (zipeado, por favor), simplemente escríbeme a:
Fejoal(eenn)hotmail.com
Para enviarlo, reemplaza "(eenn)" con @ en la dirección que te dí. Es para evitar a los programas que recolectan direcciones de e-mail (ya no saben qué inventar...)
Ten presente que durante el fin de semana no accedo a este sitio.
Un abrazo!
Fernando

Como vas.
Acerca de tu pregunta Excel tiene una función que prácticamente hace todo lo que tu quieres, la encuentras por el menu datos y se llama asistente para plantillas.
Básicamente lo que debes hacer es armar dos archivos uno con los datos (la base de datos en donde se ingresan nuevos registros que ya lo tienes) y otro con una plantilla de ingreso, algo así como un formulario (las celdas pueden estar verticalmente u horizontalmente como gustes), luego, estando ubicado en el libro que vas a usar como base (la plantilla) para capturar los datos del otro libro te vas por la opción que te mencioné... a partir de ahí el asistente te irá guiando sobre lo que debes hacer, luego de que termines el proceso tu ingresas los datos de un registro nuevo en la plantilla... le das guardar al libro (que te va a quedar con una extensión xlt y no xls como los demás) y verás como los datos que ingresaste en la plantilla aparecen como un nuevo registro (renglón o fila)en la base de datos.
Espero que sea lo que necesitas, si tienes alguna pregunta adicional o no entiendes algo me avisas y con mucho gusto te ayudo, si de lo contrario, la respuesta te satisface, te agradeceré una calificación acorde con la calidad y oportunidad de la respuesta.

Otra vez César, estoy probando, pero no me sale como lo quiero, no se si me puedas dar tu mail, para mandarte el archivo y veas que es lo que pretendo, ya que no me funciona. Gracias

Bueno la verdad no es clara tu pregunta pero no te preocupes siempre es difícil explicar problemas del software con palabras escritas, por favor aclarame la pregunta, pero quiero decirte algo, ya estas utilizando celdas con nombres definidos de tal manera que una vez llames el nombre de la celda el va a traer lo que este en ella. LO difícil es saber como tienes tu hoja y como estas llamando estas celdas, ¿y cómo lo registras?
Por favor aclarame un poco más acerca de tu pregunta

Lo que deseo, es tener en la hoja 1, celdas de captura de manera vertical, más o menos 8 celdas para registrar datos, y al momento de terminar de registrar los datos, automáticamente se pasen los datos a la hoja 2, todos los datos de manera horizontal, y cada vez que capture nuevos datos, se vayan incrementando en la hoja 2, como registros nuevos, sin borrar los datos anteriormente cargados, y en la hoja 1, pueda volver a capturar más información en las mismas celdas de captura y se vayan incrementando en la hoja 2.
Saludos!