excel - Autofill lines based off cell value -

first time poster here. i've been searching answer question, i'm having trouble finding similar situation answer works me. i'm using excel vba try solve problem work. i'm new vba environment i'm not sure how proceed. or advice appreciated!

the problem:

i have value in cell b14 on tab1. cell has count formula counts number of values on tab1. number (for example, 14), i'd go tab2 , copy formula in cells a2:h2 down number shown in cell, 14 rows.

if b14 shows 27, i'd macro auto fill a2:h2 27 rows , on. i've tried few examples similar questions couldn't work.

any advice?

since value in b14 determined via formula, you'll need monitor worksheet_calculate event determine when value changes. static variable trick. add following event handler sheet1:

private sub worksheet_calculate()      static b14_value long      if range("b14") <> b14_value          ' save new b14 value...         b14_value = range("b14")          ' fill formula down on sheet2. add 1 account offset.         sheet2.range("a2:h" & b14_value + 1).filldown      end if  end sub 

since you're starting @ row 2, we'll need add 1 value of b14 account offset.