We help IT Professionals succeed at work.

# Loop Data

on
323 Views
Hi Experts,

I would like to request Experts help to add additional function in the attached script. The macro able to copy data from Data sheet to Detail Sheet. Each time run the macro, the new data will override the old data. Is that possible to copy new data from "Data" sheet to "Detail" at new row instead of overriding the old data? Hope Experts will help me to add this new feature.
``````Sub Filter()

Dim k(), ka, I As Long, d As String, n As Long, c As Long

With Worksheets("Data")
ka = .Range("a6:d" & .Range("a" & .Rows.Count).End(xlUp).Row)
End With

ReDim k(1 To UBound(ka, 1), 1 To 5)

With CreateObject("scripting.dictionary")
.comparemode = 1

For I = 1 To UBound(ka, 1)
If IsDate(ka(I, 1)) Then d = ka(I, 1): GoTo Nxt ' Like "*(*)" Then
If Len(ka(I, 3)) Then
If .exists(ka(I, 2)) Then
If Not .exists(ka(I, 3)) Then
n = n + 1: k(n, 1) = d
For c = 1 To UBound(ka, 2)
k(n, c + 1) = ka(I, c)
Next
End If
End If
End If
Nxt:
Next
End With
If n Then
With Worksheets("Detail")
.UsedRange.Offset(1).ClearContents
Union(.Range("b2").Resize(n), .Range("e2").Resize(n)).NumberFormat = "[h]:mm"
.Range("a2").Resize(n, UBound(k, 2)) = k
End With
End If

End Sub
``````
Data-Copy.xls
Comment
Watch Question
Commented:
This problem has been solved!
###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant