i am developing a macro which will first transfer and remove. i am using concatenated fields someone help me ..
Option Explicit
Sub transferorderdata()
Dim wkbor, wkbvl, wkbysd, wkbtr As Workbook
Dim wknm, wknm1, wknm2 As String
wknm2 = "C:\vbproject\tracker\ysdflow2.xlsx"
wknm = "d:\Open.xls"
wknm1 = "d:\tracker.xlsx"
Set wkbor = Workbooks.Open(wknm)
Set wkbtr = Workbooks.Open(wknm1)
Dim irowor, irowtro, irowtrs As Integer
Dim i, j As Integer
Dim selor, seltro As String
Dim blnor As Boolean
wkbor.Sheets("Filtered").Activate
irowor = wkbor.Sheets("Filtered").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
irowtro = wkbtr.Sheets("Ordering").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
For i = 2 To irowor
For j = 2 To irowtro
wkbor.Sheets("Filtered").Activate
selor = wkbor.Sheets("Filtered").Cells(i, 1).Value + wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 9).Value
seltro = wkbtr.Sheets("ordering").Cells(i, 39).Value
If selor = seltro Then
blnor = True
Else
blnor = False
End If
If blnor = True Then
wkbtr.Sheets("Ordering").Cells(j, 7).Value = wkbor.Sheets("Filtered").Cells(i, 1).Value
wkbtr.Sheets("Ordering").Cells(j, 2).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 32).Value
wkbtr.Sheets("Ordering").Cells(j, 9).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 13).Value
wkbtr.Sheets("Ordering").Cells(j, 8).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 3).Value
wkbtr.Sheets("Ordering").Cells(j, 10).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 14).Value
wkbtr.Sheets("Ordering").Cells(j, 11).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 38).Value
wkbtr.Save
Exit For
End If
If blnor = False Then
Dim irowtro1 As Integer
irowtro1 = wkbtr.Sheets("Ordering").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
wkbtr.Sheets("Ordering").Cells(irowtro1, 39).Value = selor
wkbtr.Sheets("Ordering").Cells(irowtro1, 7).Value = wkbor.Sheets("Filtered").Cells(i, 1).Value
wkbtr.Sheets("Ordering").Cells(irowtro1, 2).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 32).Value
wkbtr.Sheets("Ordering").Cells(irowtro1, 9).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 13).Value
wkbtr.Sheets("Ordering").Cells(irowtro1, 8).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 3).Value
wkbtr.Sheets("Ordering").Cells(irowtro1, 10).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 14).Value
wkbtr.Sheets("Ordering").Cells(irowtro1, 11).Value = wkbor.Sheets("Filtered").Cells(i, 1).Offset(0, 38).Value
wkbtr.Save
Exit For
End If
Next
Next
wkbtr.Save
wkbor.Close
End Sub
it is working but at the end i am left with some duplicates entry in the final file..
some body help me out
Thanks
Akshay