Cannot jump to cells because it is hidden.

[daw60]

Sub Adddata ()
Dim lr As Variant
Sheets("RUNNING TOTAL").Select
lr = Cells.find ("*", Cells(3, 9), xlforumlas, xlPart, xlByRows, xlPrevious, False).Row
Range("I3:K&lr").Copy

Sheets("annual").Select
lrANNUAL = Cells.find ("*", Cells(1, 1), xlforumlas, xlPart, xlByRows, xlPrevious, False).Row
Cells(lrANNUAL + 1, 1).Select
ActiveSheet.Paste

End Sub

The error occurs above in red.

 

[HaHoBe]

Hi daw60,

it's xlFormulas, Range("I3:K" & lr).Copy...

Try

Sub AddData()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells.Find("*", .Cells(3, 9), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Sheets("RUNNING TOTAL").Range("I3:K" & lr).Copy .Cells(lrANNUAl + 1, 1)
End With
End Sub

Ciao,
Holger

 

[daw60]

Hi daw60,

it's xlFormulas, Range("I3:K" & lr).Copy...

Try

Sub AddData()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells.Find("*", .Cells(3, 9), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  Sheets("RUNNING TOTAL").Range("I3:K" & lr).Copy .Cells(lrANNUAl + 1, 1)
End With
End Sub

Ciao,
Holger

 

[daw60]

it works almost... there are several rows of data that should have been copied but only the first line was actually copied and pasted.

 

[HaHoBe]

Hi daw60,

and what value does lr hold?

Maybe try using either

Sub AddData_mod2()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
  .Cells(lrANNUAl, 1).Resize(lr - 2, 3).Value = Sheets("RUNNING TOTAL").Range("I3:K" & lr).Value
End With

End Sub

or

Sub AddData_mod3()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells.SpecialCells(xlCellTypeLastCell).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
  .Cells(lrANNUAl, 1).Resize(lr - 2, 3).Value = Sheets("RUNNING TOTAL").Range("I3:K" & lr).Value
End With

End Sub

Both codes will copy just the values to Sheet "annual".

Ciao,
Holger

 

[daw60]

Hi daw60,

and what value does lr hold?

Maybe try using either

Sub AddData_mod2()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
  .Cells(lrANNUAl, 1).Resize(lr - 2, 3).Value = Sheets("RUNNING TOTAL").Range("I3:K" & lr).Value
End With

End Sub

or

Sub AddData_mod3()
Dim lr As Long
Dim lrANNUAl As Long
With Sheets("RUNNING TOTAL")
  lr = .Cells.SpecialCells(xlCellTypeLastCell).Row
End With

With Sheets("annual")
  lrANNUAl = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
  .Cells(lrANNUAl, 1).Resize(lr - 2, 3).Value = Sheets("RUNNING TOTAL").Range("I3:K" & lr).Value
End With

End Sub

Both codes will copy just the values to Sheet "annual".

Ciao,
Holger

The second suggestion works perfectly.
Thank you for your help!