Be aware that sometimes
UsedRange
can be overstated. To demonstrate this use Ctrl-End in your worksheet to "find" the last cell used. Now type something in to a cell lower and to the right. Delete what you have just typed in, then select cell A1. Now use Ctrl-End again - Excel will (usually) take you the now empty cell that you just typed into and cleared out.
There are ways to reset that, that usually involve having to save the file. But in VBA you can ensure that it is always reset by referencing the UseRange at the start of your sub
ActiveSheet.UsedRange
After that, instead of using loops you should treat the
Range
as a set, and there is no need to use .Copy, you can just assign the formulae - text (i.e. anything that is not really a formula) will still be "copied" over. E.g. here is a sample module
Option Explicit
Sub Demo()
With ThisWorkbook.Sheets(1)
.UsedRange
Dim r As Long, c As Long
r = .UsedRange.Rows.Count
c = .UsedRange.Columns.Count
Dim lastcell As Range
Set lastcell = .Range("A1").Offset(r, c - 1)
Dim source As Range, target As Range
Set source = .Range("A1:" & lastcell.Address)
Set target = ThisWorkbook.Sheets(2).Range(source.Address)
target.Formula = source.Formula
End With
Set source = ThisWorkbook.Sheets(1).UsedRange
Set target = ThisWorkbook.Sheets(3).Range(source.Address)
target.Formula = source.Formula
End Sub