Copying Range Based On Certain Criteria? Possible looping through the Range

[Big H]

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

 

[Tom Ogilvy]

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.Columns(8),"CCLS") > 0
then


end if