excel - VBA Copy list of years in column up to keyword -

i'm trying create feedback sheet on excel, new vba.

i have column populated years (2014...2015 etc..) starting in cell a4 (sheet 1) continuing until cell containing "grand total"

i copy of years "grand total" cell in column sheet 2, bearing in mind year values , row locations changing...i new list on sheet 2 not include empty rows present between years on sheet 1.

any tips?

cheers, matt

give try:

sub getinfo() dim counter integer, lastrow integer counter = 1 lastrow = cells.find(what:="grand total", after:=[a4], searchorder:=xlbyrows, searchdirection:=xlprevious).row  = 4 lastrow     if cells(counter, 1).value < lastrow         counter = counter + 1     end if next  range("a4:a" & counter + 1).copy destination:=sheets("sheet2").columns(1) sheets("sheet2").activate = 1 lastrow     if cells(i, 1).value = ""         cells(i, 1).delete     end if next end sub