Hi Gurus I have been given help and the forum have been very very helpful (at the above link) the code suggested almost meets my needs, But so far the forum hasn’t found a solution to the below issue : It falls over in the line with “c.Value” for some unknown reason no one has been able to resolve this, hopefully it’ll stand out to someone what the issue with this .
ActiveSheet.UsedRange.AutoFilter 4, c.Value ''''''doesn't filter my date range 'when i change the line it to the below it will filter the date range.. ActiveSheet.UsedRange.AutoFilter 4, "24/11/2016" '''''does filter my date range
Sub makeNuSheets() Dim c As Range, sh As Worksheet, lr As Long, newSh As Worksheet Set sh = ActiveSheet lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row sh.Range("D:D").AdvancedFilter xlFilterCopy, , Range("A" & lr + 2), True For Each c In Range("A" & lr + 2).CurrentRegion.Offset(1) If c.Value <> "" Then sh.UsedRange.AutoFilter 4, c.Value Set newSh = Sheets.Add(After:=Sheets(Sheets.Count)) newSh.Name = Format(c.Value, "ddmmmyyyy") sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy newSh.Range("A1") sh.AutoFilterMode = False Set newSh = Nothing End If Next sh.Range("A" & lr + 2).CurrentRegion.ClearContents End Sub
[/code] the issue is with the part c.Value as when this is used it comes up with the filter on but no values selected, yet when I temp replace c.Value with a date it work just fine. Any help or suggestions would be more than grateful...many thanks in advances
For any interested in using or adapting this code, this issue is now resolved, by swapping out with this line
sh.UsedRange.AutoFilter 4, CStr(c.Value)credit to the below
JLGWhizBoard Regular member…Mr.Excel.Com