put headers and values in array & copy values

hi
I have data in sheet1 . some values should repeat more than one time when copy to sheet2 as DATE and ORDER based on last filled data in COL B (BRAND)
and some values are not existed at all in sheet1 it supposes creating by put the headers and the values in array and repeat the value when copy to sheet2 as ACCOUNT NO(COL D) , REF(COLF), CASE(COL L) finally if there is a macro to create all of data based on sheet1 with the same formatting and borders except the formulas shouldn't copy as formula just show as a value and what I explained .
thanks in advance