excel - Get the count from activesheet NOT from previous active sheet -

this code supposed sort column b based on duplicates , paste duplicates newly created sheet. , run same code process active sheet (sheet4) , move duplicates sheet5.

lr = cells.find("*", after:=[a1], searchdirection:=xlprevious).row 

lr getting me count previous sheet(sheet1). how make sure lr count new sheet4 , gets count active sheet?

sub duplicate() dim t single t = timer dim d object, x&, xcol string dim lc&, lr&, k(), e range dim ows1, ows2 worksheet xcol = "b" set wb = thisworkbook sheetname = activesheet.name worksheets(sheetname).activate  lc = cells.find("*", after:=[a1], searchdirection:=xlprevious).column lr = cells.find("*", after:=[a1], searchdirection:=xlprevious).row redim k(1 lr, 1 1) set d = createobject("scripting.dictionary") each e in cells(1, xcol).resize(lr)     if not d.exists(e.value)         d(e.value) = 1         k(e.row, 1) = 1     end if next e if d.count = lr     msgbox "no duplicates"     exit sub end if  cells(1, lc + 1).resize(lr) = k range("a1", cells(lr, lc + 1)).sort cells(1, lc + 1), 1 x = cells(1, lc + 1).end(4).row set tgt = wb.sheets.add(after:=sheets(sheets.count)) tgtlastrow = tgt.cells(rows.count, "a").end(xlup).row + 1  cells(x + 1, 1).resize(lr - x, lc).copy tgt.range("a" & tgtlastrow) cells(x + 1, 1).resize(lr - x, lc).clear cells(1, lc + 1).resize(x).clear msgbox "code took " & format(timer - t, "0.00 secs") msgbox lr & " rows" & vblf & lc & " columns" & vblf & _     lr - x & " duplicate rows" end sub 

i think may have found simpler method you. if sheet4 want count alter code be:

sheets("sheet4").select sheetname = activesheet.name  lc = cells.find("*", after:=[a1], searchdirection:=xlprevious).column lr = cells.find("*", after:=[a1], searchdirection:=xlprevious).row 

this count rows of activesheet:

with activesheet  application.statusbar = "counting rows" 'this lets user know happening 'you don't have include  count1 = application.counta(range("a:a")) 'if row give accurate count  end 

there many methods count. if 1 not want, please comment , i'll suggest another.