excel - Dyanmic VBA code for changing the vba when a sheet name is changed -

i have vba code specifies particular sheet names @ example sheet 2,

but if, forgot change sheet name sheet2, can add piece of dynamic code automatically change vba code ever sheet name called? example second sheet in left.

code module 1:

sub calculation()  range("p2:p800").select application.cutcopymode = false selection.clearcontents  dim dict1 object dim c1 variant, k variant dim currws worksheet dim double, lastrow double, tot double dim number1 double, number2 double, firstrow double  set dict1 = createobject("scripting.dictionary") set currws = thisworkbook.sheets("trade data")  'get last row withh data in column lastrow = currws.cells(rows.count, "m").end(xlup).row  'put unique numbers in column in dict1 c1 = range("m2:v" & lastrow) = 1 ubound(c1, 1)     if c1(i, 1) <> ""         'make combination first 4 characters       dict1(left(c1(i, 1), 4) & "," & left(c1(i, 8), 4) & "," & left(c1(i,     6), 10) & "," & left(c1(i, 10), 7)) = 1     end if next  'loop through numbers in column each k in dict1.keys     number1 = split(k, ",")(0)     number2 = split(k, ",")(1)     tot = 0     firstrow = 0      = 2 lastrow         if k = left(currws.range("m" & i).value, 4) & "," &          left(currws.range("t" & i).value, 4) & "," & currws.range("r" &      i).value & "," & (currws.range("o" & i).value)             if firstrow = 0                 firstrow =             end if             tot = tot + currws.range("w" & i).value         end if     next     currws.range("p" & firstrow) = tot next k  call consolidate call singletrademove end sub 

module 2 code: sub singletrademove()

 dim wstd worksheet  set wstd = worksheets("trade data")   sheets("unmatching").range("a2:ak600").clearcontents   wstd   lastrow = .range("a" & .rows.count).end(xlup).row   = 2 lastrow   if left(.cells(i, "m"), 4) <> left(.cells(i, "t"), 4) _     or .cells(i, "o") <> .cells(i, "v") _     or .cells(i, "r") <> .cells(i, "y")         .cells(i, "j").entirerow.copy _            destination:=sheets("unmatching").range("a" &   rows.count).end(xlup).offset(1)       end if   next  end  end sub 

say have sheet named "work data" , programmed sheets("work data"). make dynamic, can use name before parenthese when launch visual basic editor.

for example, have code:

sheets("work data").select 

now can change this:

sheet1.select 

and way, no matter how users changed sheet name, work. please remember, sheet1 can changed can done inside visual basic editor properties. can password protected vba no 1 can accidentally alter it.