Copy range based on certain conditions

[Sotomayor]

Hi all:

I have this code:

Sub transpose_UPCID()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))
Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))
rng1.Copy
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
rng2.Copy
Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True

End Sub

This code copy a whole range and paste it into another worsheet. I need help to change this code to do the following. Let me explain.

I added a column in the Sales-Inventory called Type. This column has a data validation with six options. Like this: WE,DY,SD,IU,HJ,BH. I also have a worksheet for each one of this "types". I need the code to look into the Type column and copy the values to correspondent worsheet.

All products with a WE type, copy to WE worksheet and so on. Currently the code copy a whole range to one worsheet.

Did I explain myself correctly? Can anybody help me with that? Please

Thanks

 

[Derek]

Hi there

If I understand you correctly you might like to try code something like this:

Sub test()
For Each cell In Range("TYPE")
On Error Resume Next
Sheets(cell.Value).Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Value = cell.EntireRow.Value
Next
End Sub

This assumes your "Type" column in the Sales-Inventory sheet is a named range called "TYPE". It also assumes every row that you transfer will have an entry in column A - so the macro knows where the next vacant line is to transfer data. If this is not the case, you must change A65536 in the above code to a column which will always have an entry.

The line: On error resume next, is mainly to avoid the possibility of a title header in the column named TYPE. Also it will skip rows where it cannot identify the worksheet to copy to (ie each entry in TYPE must be identical to a worksheet name in the workbook)

Regards
Derek