how to assign the correct item code and sort the data by item code and date?

[heihei]

Halo,anyone.
I would like to ask how to assign the correct item code for each record in template2?
It is because when I search 1/7/2013-15/7/2013, it output 4 records,
but the item code is not 1,2,3,4. How can I assign it correctly?

Also, I want to sort the data by item code, then sort by date, how can I do that?

Thanks, heihei




<code>

 </code><code>Option Explicit</code>
<code>Private Sub CommandButton1_Click()</code>
<code>Dim ws As Worksheet</code>
<code>Set ws1 = Sheets("Template2")</code>
<code>Set ws2 = Sheets("Database2")</code>
<code>Set ws3 = Sheets("By Period")</code>
<code>Dim i As Integer</code>
<code>Dim j As Integer</code>

<code>For i = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).row</code>
<code>If ws3.Cells(7, 3).Value >= ws2.Cells(i, 2).Value And ws3.Cells(4, 3).Value <= ws2.Cells(i, 2).Value And ws2.Cells(i, 9).Value = 0 Then</code>

<code>LR = ws1.Range("B" & Rows.Count).End(xlUp).row</code>

<code>ws1.Range("A" & LR + 1) = i - 1</code>

<code>ws2.Cells(i, 2).Copy</code>
<code>ws1.Range("B" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>
<code>ws1.Range("B" & LR + 1).NumberFormat = "dd/mm/yyyy"</code>

<code>ws2.Cells(i, 3).Copy</code>
<code>ws1.Range("C" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 4).Copy</code>
<code>ws1.Range("D" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 8).Copy</code>
<code>ws1.Range("E" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 5).Copy</code>
<code>ws1.Range("F" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 14).Copy</code>
<code>ws1.Range("G" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 6).Copy</code>
<code>ws1.Range("H" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 12).Copy</code>
<code>ws1.Range("I" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>ws2.Cells(i, 13).Copy</code>
<code>ws1.Range("J" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>
<code>ws1.Range("J" & LR + 1).NumberFormat = "dd/mm/yyyy"</code>

<code>ws2.Cells(i, 11).Copy</code>
<code>ws1.Range("K" & LR + 1).PasteSpecial Paste:=xlPasteValues</code>

<code>End If</code>

<code>Next i</code>

<code>Dim xlSort As XlSortOrder</code>

<code>With ws1</code>

<code>LR = ws1.Range("B" & Rows.Count).End(xlUp).row</code>

<code>If (.Range("C6").Value > .Range("C" & CStr(LR))) Then</code>
<code>xlSort = xlDescending</code>
<code>Else</code>
<code>xlSort = xlAscending</code>
<code>End If</code>

<code>.Range("B6:K" & LR).Sort Key1:=.Range("C6"), Order1:=xlSort, Header:=xlNo, _</code>
<code>OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _</code>
<code>DataOption1:=xlSortNormal</code>

<code>End With</code>

<code>ActiveWorkbook.Save</code>


<code>End Sub</code><code>

</code>






<fieldset class="postcontent"> <legend>

Attached Files</legend>

• 
  sample.xlsm‎ (206.6 KB, 0 views)

</fieldset>

 

[heihei]

same post by jason
how to assign the correct item code and sort the data by item code and date?

 

[heihei]

Does someone know how to solve this problem?