vba - Every time a cell's value changes, copy and paste range to next open column on sheet2 -

i have tried variety of functions try , work bit complicated skill level vba, appreciated.

i have workbook 2 worksheets, 1st having large range of data , formulas , 2nd containing 2 empty tables populated specific date sheet1. sheet1 has column of dates range("dk7:dk39") range 5/1/1986 5/1/2018. time first date changed in column ("dk7"), of other dates in following years auto-update same day in given year. these changes, 2 separate columns repopulated new data determined based on date-specific data.

i have code cycles target/first date cell ("dk7") through dates need data for:

dim date = dateserial(1986, 5, 1) dateserial(1986, 8, 28) range("dk7").value = next 

i need add code while dates being cycled through , thus, creating new data needs recorded, ranges ("ds7:ds38") & ("dt7:dt38") copied , pasted next open column in 2 respective ranges on sheet2 formatted years 1986-2017 y-axis column , 5/1 through 8/28 x-axis row.

for example when 5/1 changed 5/2 in sheet1 years 1986 through 2017, need data in ranges ("ds7:ds38") & ("dt7:dt38") copied , pasted sheets2.range("e4:e35") , ("e41:e72") before date on sheet1 loops 5/3 when need data in ranges ("ds7:ds38") & ("dt7:dt38") copied , pasted sheets2.range("f4:f35") , ("f41:f72")