Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Range Based On Certain Criteria? Possible looping through the Range
Hi There,
the problem I have is this: I have a dynamic range of data A2:H? if ColumnA = Actual, ColumnH = CCLS, within the same row or rows, I want to copy that information and paste it onto another sheet. Is there a way of looping through the rows and copying to another sheet? At present I have code (using the recorder) whereby I put a filter on the columns and set the criteria I want, then I have defined a Name (A_CCLS), which is dynamic, copy this range and paste it. The problem with this is that sometimes ColumnH will not have CCLS within it and this causes an error within the code. regards Harry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Range Based On Certain Criteria? Possible looping through the Range
If your code works well except for this one problem, then integrate a check
to see if you should copy or not. One way: ' apply your filter then set rng = Activesheet.Autofilter.Range s = rng.columns(1).Address s1 = rng.Columns(8).Address cnt = Evaluate("Sum(--(" & s & "=""Actual""),--(" & s1 & "=""CCLS""))") if cnt 1 then ' code to do the copying end if another Dim rng as Range ' after you have applied the critieria to the data and before you attempt to copy set rng = ActiveSheet.Autofilter.Columns(1) if rng.Specialcells(xlVisible).Count 1 then ' do the copy End if If it is simpler, perhaps that if CCLS exists in column H, then you will have rows to copy you could check with if Application.Countif(Activesheet.Autofilter.Range.C olumns(8),"CCLS") 0 then end if -- Regards, Tom Ogilvy "Big H" wrote in message ... Hi There, the problem I have is this: I have a dynamic range of data A2:H? if ColumnA = Actual, ColumnH = CCLS, within the same row or rows, I want to copy that information and paste it onto another sheet. Is there a way of looping through the rows and copying to another sheet? At present I have code (using the recorder) whereby I put a filter on the columns and set the criteria I want, then I have defined a Name (A_CCLS), which is dynamic, copy this range and paste it. The problem with this is that sometimes ColumnH will not have CCLS within it and this causes an error within the code. regards Harry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range of cells based on criteria in another range | Excel Worksheet Functions | |||
Copying a range based on criteria | Excel Programming | |||
Copying a range based on criteria | Excel Programming | |||
copying dynamic range based on cell outside of range | Excel Programming | |||
group rows in a range based on criteria from another range (vba) | Excel Programming |