Copy date ranges to new worksheets retaining all data in the original worksheet

[11JMP123]

I am trying to write code to copy a date range(with times included) to another worksheet in the same workbook. Example: eachdate range in column E would include only one month (say June dates) and only thatdata would go to a new worksheet. Then it would move to the next month incolumn E (say July dates) and copy that date range to a new worksheet, and so on.
Alldata on the original would remain intact. I have tried several scripts but am not gettingany results.
I am definitely a vba meddler and by no means an expert. :|

 

[DanteAmor]

Try this
Change sheet1 to the name of your master sheet. The macro will create you 12 sheets, one for each month.

Sub FilterMonth()
    Dim sh As Worksheet, sh2 As Worksheet
    Dim i As Long, lr As Long, wDay As Long
    Dim wDate As String
    
    Set sh = Sheets("[COLOR=#ff0000]sheet1[/COLOR]")
    lr = sh.Range("D" & Rows.Count).End(xlUp).Row
    For i = 1 To 12
        wDay = Format(Day(DateSerial(Year(Date), i + 1, 1) - 1), "00")
        wDate = Format(i, "00") & "/" & wDay & "/" & Year(Date)
        sh.Range("A1").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(1, wDate)
        Sheets.Add(, Sheets(Sheets.Count)).Name = i
        sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    Next
    sh.ShowAllData
End Sub

 

[11JMP123]

Try this
Change sheet1 to the name of your master sheet. The macro will create you 12 sheets, one for each month.

Sub FilterMonth()
    Dim sh As Worksheet, sh2 As Worksheet
    Dim i As Long, lr As Long, wDay As Long
    Dim wDate As String
    
    Set sh = Sheets("[COLOR=#ff0000]sheet1[/COLOR]")
    lr = sh.Range("D" & Rows.Count).End(xlUp).Row
    For i = 1 To 12
        wDay = Format(Day(DateSerial(Year(Date), i + 1, 1) - 1), "00")
        wDate = Format(i, "00") & "/" & wDay & "/" & Year(Date)
        sh.Range("A1").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(1, wDate)
        Sheets.Add(, Sheets(Sheets.Count)).Name = i
        sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    Next
    sh.ShowAllData
End Sub

Thanks for the code. Way shorter than I was trying. It worked except it changed the dates in the new worksheets (6/3/2018 became 1/1/2019) and it also did not copy columns C and D and G and H over to the new worksheets.
How can I fix this?

 

[DanteAmor]

I extended the columns to the z

Check that you have the dates all with the same date format "mm/dd/yyyy". Let them be dates and not texts.

Sub FilterMonth()
    Dim sh As Worksheet, sh2 As Worksheet
    Dim i As Long, lr As Long, wDay As Long
    Dim wDate As String
    
    Set sh = Sheets("sheet1")
    lr = sh.Range("D" & Rows.Count).End(xlUp).Row
    For i = 1 To 12
        wDay = Format(Day(DateSerial(Year(Date), i + 1, 1) - 1), "00")
        wDate = Format(i, "00") & "/" & wDay & "/" & Year(Date)
        sh.Range("A1:Z" & lr).AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(1, wDate)
        Sheets.Add(, Sheets(Sheets.Count)).Name = i
        sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    Next
    sh.ShowAllData
End Sub

 

[11JMP123]

I extended the columns to the z

Check that you have the dates all with the same date format "mm/dd/yyyy". Let them be dates and not texts.

Sub FilterMonth()
    Dim sh As Worksheet, sh2 As Worksheet
    Dim i As Long, lr As Long, wDay As Long
    Dim wDate As String
    
    Set sh = Sheets("sheet1")
    lr = sh.Range("D" & Rows.Count).End(xlUp).Row
    For i = 1 To 12
        wDay = Format(Day(DateSerial(Year(Date), i + 1, 1) - 1), "00")
        wDate = Format(i, "00") & "/" & wDay & "/" & Year(Date)
        sh.Range("A1:Z" & lr).AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(1, wDate)
        Sheets.Add(, Sheets(Sheets.Count)).Name = i
        sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    Next
    sh.ShowAllData
End Sub

Hi there DanteAmor,

I tried as you suggested with no improvement. It turns out that my data is much more complex than I realized. (different columns reflecting different dates ranges for equipment readings. Thanks for trying!

 

[DanteAmor]

You could put an example with data of what you have and what you expect of result.

 

[11JMP123]

Let me think about how I would do that. Thanks again for your help!

 

[DanteAmor]

Don't worry take your time.