excel - Simple VBA Script Error -

i'm still pretty new vba (learning work, coming js background) , need little this. goal is: loop through each worksheet (except "summary" sheet, although i'm not sure how exclude loop) in workbook , copy a2 in each sheet, last cell containing value in column l of each sheet, , paste next each other in columns , b on "summary sheet", respectively. i'm not expert vba syntax means, if has way refactor (i know don't need .select methods), appreciate it. right i'm getting "invalid or unqualified reference" error on line 28. goal learn, if have input appreciate short explanation of logic. thanks.

            sub macro7()             '             ' macro7 macro             '             ' keyboard shortcut: ctrl+c               dim ws worksheet             dim lastrow integer             dim summaryrow integer                  summaryrow = 1              each ws in activeworkbook.worksheets              'copy item number , paste on summary page'                  range("a2").select                 selection.copy                 sheets("summary").select                 range("a" & summaryrow).select                 selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _                     :=false, transpose:=false              'copy corresponding bom item # , paste on summary page'                  ws.select                 lastrow = .cells(.rows.count, "l").end(xlup).row                 range("l" & lastrow).select                 application.cutcopymode = false                 selection.copy                 sheets("summary").select                 range("b" & summaryrow).select                 selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _                     :=false, transpose:=false                  summaryrow = summaryrow + 1                      next ws             end sub 

you can avoid using .select¹ constructing with ... end statement passes parent worksheet reference along cells , range references. direct value transfer more expedient , more efficient copying , pasting. additionally, avoids involving clipboard altogether.

sub macro7()     dim ws worksheet     dim lastrow long     dim summaryrow long      summaryrow = 1      each ws in activeworkbook.worksheets         ws             if lcase(.name) <> "summary"                 worksheets("summary").range("a" & summaryrow).resize(1, 2) = _                     array(.range("a2").value, .cells(rows.count, "l").end(xlup).value)                 summaryrow = summaryrow + 1             end if         end     next ws  end sub 

---

.¹ _{see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.}