Macro para copiar un rango de celdas en excel y pasar a hoja 2

[JoaoM]

Y esas 41 filas ¿quedaran entremedio de filas ocupadas? o ¿crear 41 filas después de la última ocupada?
Explícate mejor y completo para que se entienda

A lo mejor no soy capaz de darte ayuda por mis escasos conocimientos pero, algún usuario conocedor podrá hacerlo

Edito:

Te consegui este codigo para un boton, tu verás si te sirve o no. Lo pondrás en un modulo y el comando para el boton es el primer, Sub InsertRowsAndFillFormulas_caller

Este, si la celda selecionada contiene formula, la coservará y copiará la formula a las celdas siguientes creadas

Sub InsertRowsAndFillFormulas_caller()
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) 'Agrega cantidad de lineas hacia abajo segun la celda seleccionada
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' selección de fila basado en la celda activa
Dim x As Long
ActiveCell.EntireRow.Select 'Segun celda selecionada, seleciona toda la fila
If vRows = 0 Then
vRows = Application.InputBox(prompt:="How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Por defecto presenta 1, una sola fila. Puedes decirle cuantas quieres
If vRows = False Then Exit Sub
End If

'si sólo quiere agregar celdas y no filas enteras, elimine EntireRow "en la siguiente línea

Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook.Windows(1).S electedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count 'corrección última celda

Selection.Resize(rowsize:=2).Rows(2).EntireRow.Res ize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'manejar posible error
' Agrega filas aun conservando la o las formula(s) de la(s) celda(s) celecionada
Selection.Offset(1).Resize(vRows).EntireRow.Specia lCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

Si colocas en S. google esto, macro insertar filas en excel verás miles de opciones

Puede que este te sea mas simple

Sub Insertar_filas()
'
' Insertar el numero de filas dado por el usuario
Dim a As Integer
a = 0
a = InputBox("Cuantas filas quieres insertar? ")
While a > 0
ActiveCell.Offset(1, 0).Range("a1").Select
Selection.EntireRow.Insert
a = a - 1
Wend
End Sub