VBA - Cut and paste based on cell colour

[Taff]

Hi Guys,

looking for some help with some VBA code, which involves cutting and pasting a row dependent on a cell colour.

there are 2 actions required, which will lead me on to a few others that I will require to write, but any help with these would point me in the right direction to complete those.

Actions required:

1.Check column AQ, if any cell is the colour green, then copy the whole row and paste in a worksheet called Check. There will be several entries per session, I also need the rows pasted in the worksheet to run in a timely order, ie not copied over the last entry?

2. Check column AI and if the value in a cell is between 1.02 and 2.20, then copy whole row and paste into a worksheet called XGOA.


as always any assistance greatly appreciated.


Cheers

Taff

 

[Taff]

Hi all,

just wondered if anybody could point me the right direction on this query?

Cheers

Taff

 

[vcoolio]

Hello Taff,

See if the following code does the task for you:-

Sub Test()
        
        Dim wsM As Worksheet: Set wsM = Sheets("Master")
        Dim wsC As Worksheet: Set wsC = Sheets("Check")
        Dim wsX As Worksheet: Set wsX = Sheets("XGOA")

Application.ScreenUpdating = False

With wsM.[A1].CurrentRegion
        .AutoFilter 35, ">=" & 1.02, xlAnd, "<=" & 2.2
        .Offset(1).EntireRow.Copy wsX.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
        .AutoFilter 43, vbGreen, 8
        .Offset(1).EntireRow.Copy wsC.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
End With
          
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I'm not sure what shade of green you're using but this code should set you onto the right path.

I hope that this helps.

Cheerio,
vcoolio.

 

[Taff]

Hi & thanks for this,

unfortunately when I try to run it I am getting a Run time error '1004: Autofilter method of Range class failed

where would be the best place to try and find the resolution?

Is there a resource with definitions that I could maybe reference?

Thanks

Taff

 

[Taff]

As well as the above query to resolve, for my own education I am also trying to understand how the code works, so I have written what I believe the correct actions of line is.

Would it be possible for somebody to check, and correct if required? I don't under stand the instruction .End(3)(2)

Sub Test()
        
        Dim wsM As Worksheet: Set wsM = Sheets("Match Summaries") ' base data held here
        Dim wsC As Worksheet: Set wsC = Sheets("Check") ' Worksheet for rows with coloured cells in column AQ Match Summaries
        Dim wsX As Worksheet: Set wsX = Sheets("XGOA") ' Worksheet for rows with values between 1.02 & 2.2 from column AI in Match Summaries

Application.ScreenUpdating = False

With wsM.[A1].CurrentRegion
        .AutoFilter 35, ">=" & 1.02, xlAnd, "<=" & 2.2 'filter rows with vaules between 1.02 & 2.2 in column AI in Match Summaries
        .Offset(1).EntireRow.Copy wsX.Range("A" & Rows.Count).End(3)(2) ' Copy Rows to Worksheet XGOA
        .AutoFilter  ' reset all filters?
        .AutoFilter 43, vbGreen, 8  'filter rows with cells coloured green in Column AQ in Match Summaries
        .Offset(1).EntireRow.Copy wsC.Range("A" & Rows.Count).End(3)(2) 'copy rows to Worksheet Check
        .AutoFilter  'reset all filters?
        
End With
          
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Cheers


Taff

 

[shg]

Range.End() is equivalent to selecting a cell in the UI and doing Ctrl+(Left/Right/Up/Down) Arrow

The four associated named constants are xlToLeft (-4159), xlToRight (-4161), xlUp (-4162), and xlDown (-4121)

Someone discovered that 3 works the same as xlUp, and used that instead, just to prove they could be clever in obscuring their code. That's the Range.End(3)

The trailing 2 is the row index, so it's equivalent to .Offset(1)

So it could have instead been written Range.End(xlUp).Offset(1), but my usual practice would be Range.End(xlUp)(2)

 

[shg]

For the same reason,

.AutoFilter 43, vbGreen, 8

... would be, more readably,

.AutoFilter 43, vbGreen, xlFilterCellColor

 

[Taff]

Cheers SHG,

makes sense now... don't suppose you'd know why I would be getting a error msg "AutoFilter method of Range class failed" this is showing on the line

.AutoFilter 35, ">=" & 1.02, xlAnd, "<=" & 2.2

cheers

Taff

 

[shg]

You'd get that error if the cell area was less than 35 columns wide.

 

[Taff]

Mmmm, slightly confused - again

the worksheet has 43 columns in it, and we are asking it to look at column 35 so am I missing something?

Taff