• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      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
      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

      JLGWhizJLGWhiz is offlineBoard Regular member…Mr.Excel.Com

      https://www.mrexcel.com/forum/excel-questions/1002952-visual-basic-applications-lopp-through-abd-copy-data-up-blank-row-new-sheet-2.html#post4814691

       

      Asked by Gint32 on May 3, 2017 in VBA: Macros.
      0 Answers