Hello everyone. I have a workbook with 3 worksheets, i would like a macro what would loop each worksheet, find the last row with formulas and copy it down to the next row. Unfortunately, the three tabs are not uniform. I have attached a spreadsheet example to better clarify what i am trying to do. I have highlighted the row where i would like to the formulas copied to. I was using the following code, which worked great for just one worksheet, but wasnt dynamic or flexible enough when i wanted to loop through several worksheets with data in different locations:
Code
Sub appendToEnd()
Dim myLastCell As Range
Set myLastCell = LastCell(Worksheets("Sheet1").Range("A:W"))
Dim lastCellCoords As String: lastCellCoords = "A" & myLastCell.Row & ":W" & myLastCell.Row
Dim firstEmptyRow As Integer: firstEmptyRow = myLastCell.Row + 1
Dim firstEmptyCoords As String: firstEmptyCoords = "A" & firstEmptyRow & ":W" & firstEmptyRow
If Not myLastCell Is Nothing Then
' Now Copy the range:
Worksheets("Sheet1").Range(lastCellCoords).Copy
' And paste to first empty row
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
Else
MsgBox ("There is no data in specified range")
End If
End Sub
Function LastCell(r As Range) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
Dim LastRow&, lastCol%
On Error Resume Next
With r
' Find the last real row
LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
' Find the last real column
lastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
' Finally, initialize a Range object variable for
' the last populated row.
Set LastCell = r.Cells(LastRow&, lastCol%)
End Function
Display More
I appreciate any and all help.