Move Range with a date to a Matching Monthly Worksheet

[RyanH]

I believe this may be a simple loop, but I am not sure how to finish it. I
have a Worksheet("Archive") that contains a history of all product orders.
The entire Worksheet is Sorted by ship date (January --> December). In the
same workbook I have a WorkSheet for every month of the year, named:
"January", "Febuary", "March", etc. What I want to do is build a loop that
will scan down Sheets("Archive").Column(L:L), which contains the ship date,
and Cut the Range("A") and paste values only in the month the date belongs.
Is this possible?

Note: The ship dates are in this format, m/dd/yy.

I'm sure this loop will work much faster if there is a Do...Until loop,
because the Worksheet("Archive") is sorted by ship date!

 

[Joel]

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) <> ""
.Range("A" & RowCount & "" & RowCount).Copy
mnthname = Format(.Range("L" & RowCount), "mmmm")
With Sheets(mnthname)
If .Range("A1") = "" Then
.Paste Destination:=.Range("A1")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
.Paste Destination:=.Range("A" & NewRow)
End If
End With
RowCount = RowCount + 1
Loop

End With

End Sub

 

[RyanH]

Thanks for the quick response, but I am getting an error: "Subscript out of
range"
I tried stepping through it, but can't seem to seem what is wrong.

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) <> ""
.Range("A" & RowCount & "" & RowCount).Copy
mnthname = Format(.Range("L" & RowCount), "mmmm")
With Sheets(mnthname) <=======ERROR ERROR
If .Range("A1") = "" Then
.Paste Destination:=.Range("A1")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
.Paste Destination:=.Range("A" & NewRow)
End If
End With
RowCount = RowCount + 1
Loop

End With

End Sub

 

[Joel]

Add a msgbox

mnthname = Format(.Range("L" & RowCount), "mmmm")
msgbox(mnthname)
With Sheets(mnthname) <=======ERROR ERROR


the are two possibilities.
1) The worksheet name doesn't match a month name. You misspelled the month
name. Learn to spell.
2) The Date is not a serial date on the worksheet, but a string. Then

mydate = datevalue(.Range("L" & RowCount))
mnthname = Format(mydate, "mmmm")

 

[RyanH]

Oops!! Feb. was spelled wrong. You're the man!

Add a msgbox

mnthname = Format(.Range("L" & RowCount), "mmmm")
msgbox(mnthname)
With Sheets(mnthname) <=======ERROR ERROR


the are two possibilities.
1) The worksheet name doesn't match a month name. You misspelled the month
name. Learn to spell.
2) The Date is not a serial date on the worksheet, but a string. Then

mydate = datevalue(.Range("L" & RowCount))
mnthname = Format(mydate, "mmmm")

 

[Dan R.]

Try this one:

Sub test()
For Each cell In Sheets("Archive").Range("L1:L1000")
If cell <> "" Then
mo = Format(cell, "mmmm")
With Sheets(mo)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With
Range(Cells(cell.Row, 1), Cells(cell.Row, 16)).Copy _
Sheets(mo).Cells(lRow, 1)
End If
Next cell
End Sub