paste in different range on condition

aisietie · Mar 9th 2012

Hello,
In the file in the attachement, names are copied to different sheets on a condition that is in column 2.
(Macro Searchonstring)
Example all rows with FS in column 2 are copied to sheet FS, with CF in column 2 are copied to Sheet CF,...
But I have a 3th column that can contain 2 values: OK or NOK
What I want is that rows with FS in column 2 are copied to sheet FS, this works already, but then those with OK in column 3 must be pasted in the Range A1:A10, those with NOK must be pasted in the range A11:A20
Thanks for helping
aisietie

venkat1926 · Mar 11th 2012

Re: paste in different range on condition

why not try this macro (only one macro not three macros-if you want you can put one form button and assign this macro to that button)

but make sure each set of F etc and ok or nok are less than TEN rows

Code

Sub test()
Dim rb As Range, rc As Range, unq As Range, cunqb As Range, cunqc As Range
Dim rdata As Range, filt As Range
With Worksheets("data")
Set rdata = .Range("A1").CurrentRegion
Set rb = Range(.Range("B1"), .Range("B1").End(xlDown))
Set rc = rb.Offset(0, 1)
Set unq = .Range("A1").End(xlDown).Offset(5, 0)
rb.AdvancedFilter xlFilterCopy, , unq, True
rc.AdvancedFilter xlFilterCopy, , unq.Offset(0, 1), True
Set unq = Range(unq.Offset(1, 0), unq.End(xlDown))
For Each cunqb In unq
For Each cunqc In unq.Offset(0, 1)
rdata.AutoFilter field:=2, Criteria1:=cunqb.Value
rdata.AutoFilter field:=3, Criteria1:=cunqc.Value
Set filt = rdata.SpecialCells(xlCellTypeVisible)
filt.Copy
If cunqc = "OK" Then
With Worksheets(cunqb.Value)
.Range("a1").PasteSpecial
End With
ElseIf cunqc = "NOK" Then
With Worksheets(cunqb.Value)
.Range("a11").PasteSpecial
End With
End If
.AutoFilterMode = False
Next cunqc
Next cunqb
.AutoFilterMode = False
End With


End Sub

Display More

Code

Sub Undo()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Data" Then GoTo nextws
ws.Cells.Clear
nextws:
Next ws
End Sub

aisietie · Mar 12th 2012

Re: paste in different range on condition

Thank You.
This is what I need.

Great Job

Thanks

aisietie

paste in different range on condition

Participate now!

Share