Excel 2010 VBA code to reference multiple cells from one worksheet into the summary workshet -

i have huge spreadsheet contains "summary" worksheet, has row each worksheet in spreadsheet. of worksheets (except master , template) named based on value in column d (person's last name).

i want run macro reference following cells in reverse. (i want reference cells specific sheet dsht)

here coding have going 1 way.

i can't figure out how make go other way specific row selected. (say have row 150 selected, value in column d name of worksheet, , want reference cells worksheet in columns located in dsht.

but don't want value pasted want cell referenced if update on specific page updates on summary page. i'm manually typing =lastname!e3 each cell / column.

dim lastrw long, rw long, cnt long dim dsht worksheet, tsht worksheet dim makebooks boolean, savepath string application.screenupdating = false 'speed macro execution application.displayalerts = false 'no alerts, default answers used

set dsht = sheets("all accidents & incidents") 'sheet data on starting in row2 set tsht = sheets("template") 'sheet copy , fill out 

' determine last row of data loop through rows 1 @ time

lastrw = dsht.range("a" & rows.count).end(xlup).row tsht.copy after:=worksheets(worksheets.count) 'copy template activesheet 'fill out form 

' edit these rows fill out form, add more needed

    ' case number     .range("e3").value = dsht.range("n" & lastrw).value     ' case #     .range("e4").value = dsht.range("p" & lastrw).value     'case #     .range("e5").value = dsht.range("r" & lastrw).value     'case #     .range("e6").value = dsht.range("t" & lastrw).value     'case #     .range("e7").value = dsht.range("v" & lastrw).value      'pmts     .range("h27").value = dsht.range("x" & lastrw).value     ' pmts     .range("h38").value = dsht.range("y" & lastrw).value     ' pmts     .range("h46").value = dsht.range("z" & lastrw).value     'billback     .range("h16").value = dsht.range("aa" & lastrw).value     'company pmts     .range("h65").value = dsht.range("ab" & lastrw).value      'net loss     .range("h77").value = dsht.range("ac" & lastrw).value     'total cost     .range("h78").value = dsht.range("ad" & lastrw).value      'description     .range("a81").value = dsht.range("am" & lastrw).value       activesheet.name = .range("i3").value