Sub Ga()
Dim a As Range
  For Each a In Worksheets("Лист1").Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    Worksheets("Лист2").Range(a.Address).Value = a.Value
  Next
End Sub

Sub Ga()
Static di As Object
Dim a
  If di Is Nothing Then
    Set di = CreateObject("scripting.dictionary")
    For Each a In Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Areas
      di.Add a.Address(0, 0), a.Value
    Next
    MsgBox "Активируйте лист для вставки и запустите макрос еще раз", vbInformation
    Exit Sub
  Else
    Application.ScreenUpdating = False
    For Each a In di.keys
      Range(a).Value = di(a)
    Next
    Application.ScreenUpdating = True
    Set di = Nothing
  End If
End Sub