Excel VBA to copy each block of same datesvalues to new sheets

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
sh.Range("A" & lr + 2).CurrentRegion.ClearContents
End Sub
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

JLGWhizJLGWhiz is offlineBoard Regular member…Mr.Excel.Com



Top Contributor Asked on May 3, 2017 in VBA: Macros.
Add Comment
0 Answer(s)
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.