Copy range based on certain conditions

Sotomayor

New Member
Joined
Oct 18, 2005
Messages
40
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,299
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top