excel vba - VBA macro - Specifying new worksheet for data generated -

at moment have piece of code can take start , end dates, , list dates in date range. allows me take following table , split down individual dates.

|name            | startdate  | enddate |bob builder | 20/05/2015 | 24/05/2015  |tiny tim        | 08/06/2015 | 09/06/2015 |dolly parton    | 06/08/2015 | 08/08/2015 

however @ moment dumps created data directly below existing table this:

|name            | startdate  | enddate |bob builder | 20/05/2015 | 24/05/2015  |tiny tim        | 08/06/2015 | 09/06/2015 |dolly parton    | 06/08/2015 | 08/08/2015 |bob builder | 20/05/2015 |  |bob builder | 21/05/2015 | |bob builder | 22/05/2015 |  |bob builder | 23/05/2015 |  |bob builder | 24/05/2015 |  |tiny tim        | 08/06/2015 |  |tiny tim        | 09/06/2015 |  |dolly parton    | 06/08/2015 |  |dolly parton    | 07/08/2015 | |dolly parton    | 08/08/2015 | 

i've tried several ways of trying locate on new sheet , each time muck code fails. please note copying generated data won't work, each time run macro original table have different number of rows. code i've been using:

sub separatedaterange()  dim ws worksheet dim ncol integer   set ws = activesheet  ncol = 1   application.screenupdating = false   = 1 activesheet.cells(rows.count, ncol + 2).end(xlup).row - 1 step 1       j = 0 ws.cells(i + 1, ncol + 2).value - ws.cells(i + 1, ncol + 1).value step 1     ws.cells(ws.cells(rows.count, 1).end(xlup).row + 1, 1)     k = 0 ncol - 1 step 1         .offset(0, k).value = ws.cells(i + 1, k + 1).value     next k      .offset(0, ncol).value = dateserial(year(ws.cells(i + 1, ncol + 1).value), month(ws.cells(i + 1, ncol + 1).value), day(ws.cells(i + 1, ncol + 1).value) + j)     end      next j next  application.screenupdating = true  end sub 

the issue experiencing comes fact putting generated values @ offset of ws have defined active sheet set ws = activesheet

this reflected here:

with ws.cells(ws.cells(rows.count, 1).end(xlup).row + 1, 1) k = 0 ncol - 1 step 1     .offset(0, k).value = ws.cells(i + 1, k + 1).value next k      .offset(0, ncol).value = dateserial(year(ws.cells(i + 1, ncol + 1).value), month(ws.cells(i + 1, ncol + 1).value), day(ws.cells(i + 1, ncol + 1).value) + j) end    

instead of using active sheet, define new sheet insert values , reference in with example:

dim newws worksheet set newws = sheets("sheetname")  newws.cells(newws.cells(newws.rows.count, 1).end(xlup).row + 1, 1) 

this should put on right track.