Can i use loop? or how can i fulfill this requirement? Pls help!

[htoh2]

Greetings to all experts,

I am a novice in ms excel macro and currently i've met some difficulties in coding to meet my requirement. So, i would like to seek advices from the experts here.

In my SourceFile.xls, i have 2 columns with values as belows:

UserID CompanyCode
Helen ABC
Peter ABC
Mary CDE
Allan EFG

And in my DestinationFile.xls, i have 2 columns with values as belows:

New User ID CompanyCode



And previously, thanks to help from an expert, i'm able to fulfill the requirement :

When you filter in SourceFile.xls e.g. Company Code ="ABC" , the values(Helen,Peter) who are from Company Code = "ABC" will be stored in the DestinationFile.xls as below:


New User ID CompanyCode
Helen ABC
Peter ABC


Hence, now i have a requirement such that:

After copying the values for Company Code = "ABC" from SourceFile.xls to DestinationFile.xls, it will check the next cell in SourceFile.xlsfor a different company code for e.g Company Code = "CDE" so that it will copy the value into the DestinationFile.xls (as below),and so on.

New User ID CompanyCode
Mary CDE


Your help will be greatly appreciated!
Feel free to clear your doubts if there is any!
Thanks!



Best Regards,
htoh2

 

[SydneyGeek]

Could you post the current code please?

Denis

 

[htoh2]

Hi, thank you for your response.

The following is my current codes:


Sub Test()
Dim lr As Long
Dim dr As String
lr = Workbooks("SourceFile.xls").Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
'
Workbooks("SourceFile.xls").Activate
With Sheets("Sheet1").Columns("A:B")

.AutoFilter
.AutoFilter Field:=2, Criteria1:="ABC"
Range("A2:B" & lr).Copy
End With
Workbooks("DestinationFile.xls").Activate
Sheets("Sheet1").Range("A2").Select
ActiveSheet.Paste
dr = Join(Array(Range("A2") & ", " & Range("A3") & ", " & Range("A4") & ", " & Range("A5")))
Range("A1").Value = dr

Rows("1:1").Insert Shift:=xlDown
Range("A1").Formula = "New UserID"
Range("B1").Formula = "Company"
Range("B2").Value = Range("B3").Value

Do While Range("A3").Value <> ""
Range("A3").EntireRow.Delete Shift:=xlUp
Loop
Windows("SourceFile.xls").Activate
Cells.AutoFilter
End Sub


Best regards,
htoh2