Excel - Copiar la última hoja

hola....espero que todos estén re bien y que éste año sea el mejor para todos.....

ahora bien voy al grano....
el problema que tengo es el siguiente:
Necesito copiar la ultima hoja (hoja2) y luego crear una macro que me genere la hoja 3, luego la hoja 4 y asi....bueno escribi lo siguiente:

Sheets("hoja 1").Copy After:=Sheets(Sheets.Count)

esto me genera la hoja 2 con los datos de la hoja 1, el problema es cuando quiero generar la hoja 3, se genera como nombre pero se pegan los datos de la hoja 1 y lo que necesito son los de la hoja 2 y asi sucesivamente.....

existirá una formula (debe existir) que sea pege la ultima hoja generada?????
gracias...

No existe fórmula para hacer eso que deseas realizar.
Debes crear tu propia macro para hacerlo. El problema que tienes es que has puesto fija la Hoja 1 para hacer la copia. Debes realizar un conteo de las hojas de forma que detectes cuál es la última y copiarla.

NHojas = Sheets.Count
NombreHoja = ""
For i = 1 TO NHojas
IF Sheets(i).Name > NombreHoja THEN NombreHoja = Sheets(i).Name
Next i
Sheets(NombreHoja).Copy After:=Sheets(Sheets.Count)

Esto es un ejemplo de cómo debes realizar la macro, pero tal como está escrita el sistema confundirá los nombres Hoja1, Hoja11, Hoja2, de forma que la Hoja 2 es superior a la Hoja 11 y copiará la Hoja 2 en vez de la Hoja 11.
Te aconsejo que uses un sistema que impida este error:
- Nombres de hoja con tres dígitos Hoja 001 (si vas a tener hasta 999 hojas)
- Mejora el código de la macro.
- Mantén siempre ordenadas las hojas de forma que sólo es necesario este código

NombreHoja = Sheets(Sheets.Count).Name
Sheets(NombreHoja).Copy After:=Sheets(Sheets.Count)


Saludos

'Puedes agregar esta función en un Módulo dentro del codigo de tu Libro de excel
'Esta función Devuelve Verdadero si no encuentra coincidencia alguna con el
'nombre que le pases a través de la variable nombre_hoja

'Por ejemplo si deseas comprobar que el nuevo nombre de la hoja no se repite, y
'el nombre que deseas verificar es "Hoja Nueva" entonces llamarías a la función

If Nombre_Unico ("Hoja Nueva") = True Then
MsgBox "El nombre (Hoja Nueva) puede ser aplicado a la nueva hoja"
'Aqui pones el código para crear o copiar la nueva hoja
End If

'*******************************************************************************
'** Funcion para comprobar la unicidad de un nombre para Hoja nueva **
'** Copia despues de esta línea de asteríscos **
'*******************************************************************************
Function Nombre_Unico(nombre_hoja As String) As Boolean
Dim hojaX As Worksheet
Dim NombreHoja As String

NombreHoja = Trim(nombre_hoja)

'Inicializamos el valor de la fucnión a Verdadero,
'si no se presenta una coincidencia con el nombre
'que se recibe en esta funcion, la función devolverá True

Nombre_Unico = True

For Each hojaX In Worksheets
'Esta condición compara en por cada hoja, el nombre (nombre_hoja )que le pasamos a esta función.
If hojaX.Name = NombreHoja Then
'Si se encontró algún nombre que coincidiera con el que recibimos en esta función
'la función devolvera False y a través de Exit Function se finaliza la función, ignorando
'cualquier código que le suceda
Nombre_Unico = False
Exit Function
End If
Next hojaX
End Function