Recording an excel macro using a workbook the has a changing name -

Recording an excel macro using a workbook the has a changing name -

in excel 2010, have requirement after clicks move or re-create new workbook, run next statement on new workbook:

sub csacolorformattingssrs2008() activeworkbook.colors = workbooks("book4").colors end sub

the problem "book4" previous workbook sheet came from, workbook name can change. there way changing workbook name copying from? trying create button run macro everyone, past book names different.

you can this, button re-create operation them , sets colors.

option explicit sub copysetcolor() dim strwkb string, strsheet string strwkb = thisworkbook.name strsheet = activesheet.name 'get workbook name re-create -> ensure 2 workbooks open, , 1 want re-create dim wkb workbook, wkbcopyto workbook each wkb in workbooks if wkb.name <> strwkb set wkbcopyto = wkb exit end if next sheets(strsheet).copy before:=wkbcopyto.sheets(1) csacolorformattingssrs2008 strwkb end sub sub csacolorformattingssrs2008(strname string) activeworkbook.colors = workbooks(strname).colors end sub

excel excel-vba excel-2007