Hi everyone,
I have put the code below into a macro:
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg4 As Range
Dim rg5 As Range
'HOBART
Set rg1 = Range("AF53", Range("AF53").End(xlDown))
Set rg2 = Range("AM53", Range("AM53").End(xlDown))
Set rg3 = Range("AN53", Range("AN53").End(xlDown))
Set rg4 = Range("AM53")
Set rg5 = Range("AN53")
rg1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rg4, Unique:=True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
'LAUNCESTON
Set rg1 = Range("V53", Range("V53").End(xlDown))
Set rg2 = Range("AC53", Range("AC53").End(xlDown))
Set rg3 = Range("AD53", Range("AD53").End(xlDown))
Set rg4 = Range("AC53")
Set rg5 = Range("AD53")
rg1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rg4, Unique:=True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
(I was going to put a screenshot of the data here but couldn't make the upload work...)
Here are the issues:
1 The first time I run it the Hobart part works well but the Launceston part fails at the AdvancedFilter saying that "the extract range has a missing or illegal field name"
2 The second time I run it, it fails at the first AdvancedFilter saying that "the extract range has a missing or illegal field name"
3 When AdvancedFilter works, it enters the first value twice
Any ideas? Your help will be much appreciated.
Bookmarks