Hi folks,
My head is rattled with the piece of code below:-
[vba]path = "C:\test File.xls"
Set Wkb = Workbooks.Open(path)
ThisWorkbook.Activate
dnum = Array("3", "4", "6", "7", "8", "11", "15", "16", "17", "18", _
"29", "38", "41", "62")
dep = Array("Site 3", "site 4", "Site 6", "Site 7", "Site 8", _
"Site 11", "Site 15", "Site 16", "Site 17", "Site 18", _
"Site 29", "Site 38", "Site 41", "Site 62")
ActiveSheet.Cells(1, 1).Select
For iii = LBound(dnum) To UBound(dnum)
For iiii = LBound(dep) To UBound(dep)
Selection.AutoFilter Field:=1, Criteria1:=dnum(iii)
Selection.CurrentRegion.Copy Destination:=Wkb.Sheets(dep(iiii)).Cells(1, 1)
Next iiii
Next iii[/vba]
To explain a little further, In the main workbook I need to perform filters on each depot number and then copy that into the workbook Test.xls on the corresponding worksheet. Everything seems to work fine except where it comes to copying the data to the Test.xls worksheets. I just seem to get the filter for dnum 52 copied to each sheet??
Any suggestions, I'm going nuts and developing a classic 1000yd stare with this!!
Thanks