Copy only rows with data to last empty row on a different sheet while ignoring formulas

Question

I'm trying to copy rows with data from a Master sheet and pasting them into the last empty row of History sheet. Each row will be one input and the data start on cells between A3:J3. Some of the cells have formulas on it which is causing issues with how i find the last row with data, both on the Master sheet and History sheet. Both Sheets have the same format. This is as far as i got:

Sub CopyToAnotherSheet()
Sheets("Master").Range("A3:J50").Copy
Sheets("History").Activate
Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = ActiveSheet
    Set rng1 = ws.Columns(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
          rng1.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

It will copy a range and paste it at the end of the last range copied across.

first empty after end of current data as opposed to last empty? Are you actually trying to find the last used row in any given sheet? — QHarr
Yes, last used row on sheet History so i can paste the rows with data from Master . — Andre Pereira

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2018-02-04T11:23:43

Try changing your rng1 = line to:

Set rng1 = ws.rows(ws.Columns(1).Find("*", SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows, LookIn:=xlValues).Row + 1)

Copy only rows with data to last empty row on a different sheet while ignoring formulas

1 Answers