We help IT Professionals succeed at work.
Get Started

Loop Data

Cartillo
Cartillo asked
on
323 Views
Last Modified: 2012-05-12
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
        
        .Add "Type 1", Nothing
        .Add "Type 3", Nothing
        
        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
                        .Add ka(I, 3), Nothing
                    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

Open in new window

Data-Copy.xls
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
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

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

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

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE