excel - Copy data from one sheet to another with the cell value -


i have sheet "data". sheet, looking column k. if red in colour extract complete row , copy them sheet " delay".

i following below code. code not have error but, copies 4 rows of red while have 12 rows.

could me find wrong , changes need?

sub delay() dim cell range dim nextrow long dim double  application.screenupdating = false = application.worksheetfunction.counta(sheets("data").range("k:k")) each cell in sheets("data").range("k5:k" & a) if cell.displayformat.interior.color = vbred nextrow = application.worksheetfunction.counta(sheets("delayed").range("k:k")) rows(cell.row).copy destination:=sheets("delayed").range("a" & nextrow + 1) end if  next application.screenupdating = false end sub 

first of all:
worksheetfunction.counta counts number of cells not empty , values within list of arguments, can't use count total number of rows or find number of last row (unless cells not empty).
might need is:

nmbrows = workbook("workbookname").worksheet("sheetname").range("k" & rows.count).end(xlup).row 

or less blunt.
using counta can result in constriction of range of search lead in data loss or, in case, inserting row in incorrect place.
example:
result of

option explicit sub test()     dim long     = application.worksheetfunction.counta(thisworkbook.sheets("Ëèñò1").range("a:a"))     thisworkbook.sheets("Ëèñò1").range("b1").value = end sub 

is
enter image description here
second:
cautious, add reference each range, sheet as

thisworkbook.sheets("data").range("k5:k" & nmbrows) 

by doing can you're refering correct range want check.

another note:
i'm not vba expert if count number of rows each sheet respectively @ start of macro, in loop use nextrow=nextrow + 1 construction instead of calling function each time.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -