Hi All...
I Am New to macro's.... I have a Requirement in Excel to be done in Macro... i am trying to do it... But i am unable to figure out how to do... Any Help will be Appreciated...
My requirement is Like This...
DataInput: (Sheet1)
IN OUT Item Unit Date Location
---------------------------------------------------------------------
AB XY Item1 No's 12-5-2013 IND
CD BV Item1 No's 12-5-2013 IND
ItemRouting:
Item Routing
----------------------
Item1 Item1Route
Item2 Item2Route
Routing:
Routing IN OUT RoutingSteps Department
------------------------------------------------------
Item1Route AB XY Step1 Procurement
Item1Route AB XY Step2 Selling
Item2Route CD BV Step1 Procurement
Item2Route CD BV Step2 Selling
From the above data
1. I Have to Select a Routing(ColumnName)(From ItemRouting(Sheet Name)) based on Item (From DataInput(Sheet name))
2. Based on the Routing Selected From Step1,IN,Out(From DataInput) select the Routingsteps,Department(From Routing)
3.Based on the Department Selected i Have to copy the details in that particular Department(Sheet)...
For the Above example the output will be:
Procurement(Sheet):
IN OUT Item Unit Date Location RoutingStep Department
--------------------------------------------------------------------------
AB XY Item1 N0's 12-5-2013 IND Step1 Procurement
CD BV Item1 N0's 12-5-2013 IND Step1 Procurement
Selling:
IN OUT Item Unit Date Location RoutingStep Department
--------------------------------------------------------------------------
AB XY Item1 N0's 12-5-2013 IND Step2 Selling
CD BV Item1 N0's 12-5-2013 IND Step2 Selling
The code which i used is:
Option Explicit
Sub FindMacro()
Dim DataInputRowCount As Long, RoutingRowCount As Long, DRowCount As Long
Dim IpProductType As String, DataIN As String, DataOut As String, Routing As String, Department As String, RoutingStep As String
Dim I As Long, R As Long, Rowno As Long
Sheets("DataInput").Activate
DataInputRowCount = Cells(Cells.Rows.Count, 5).End(xlUp).Row
For I = 2 To DataInputRowCount
Sheets("DataInput").Activate
IpProductType = Cells(I, 5).Value
DataIN = Cells(I, 3).Value
DataOut = Cells(I, 4).Value
Rows(I).Copy
Rowno = Sheets("ProductRouting").Columns(1).Find(IpProductType, , xlValues, xlWhole).Row
Routing = Sheets("ProductRouting").Range("B" & Rowno).Value
Sheets("Routing").Activate
RoutingRowCount = Cells(Cells.Rows.Count, 1).End(xlUp).Row
For R = 2 To RoutingRowCount
Sheets("Routing").Activate
If Cells(R, 1).Value = Routing And Cells(R, 2).Value = DataIN And Cells(R, 3).Value = DataOut Then
RoutingStep = Cells(R, 4).Value
Department = Cells(R, 5).Value
Sheets(Department).Select
DRowCount = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Range("A" & DRowCount + 1).Select
ActiveSheet.Paste
Range("O" & DRowCount + 1).Value = RoutingStep
Range("P" & DRowCount + 1).Value = Department
End If
Next
Next
End Sub
Any Help will be Appreciated...