Move Range with a date to a Matching Monthly Worksheet
 

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:P") 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!

Move Range with a date to a Matching Monthly Worksheet
 

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) < ""
.Range("A" & RowCount & ":P" & 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" wrote:

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:P") 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!

Move Range with a date to a Matching Monthly Worksheet
 

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 & ":P" & 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" wrote:

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) < ""
.Range("A" & RowCount & ":P" & 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" wrote:

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:P") 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!

Move Range with a date to a Matching Monthly Worksheet
 

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" wrote:

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 & ":P" & 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" wrote:

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) < ""
.Range("A" & RowCount & ":P" & 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" wrote:

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:P") 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!

Move Range with a date to a Matching Monthly Worksheet
 

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

"Joel" wrote:

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" wrote:

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 & ":P" & 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" wrote:

Sub movebydate()

RowCount = 1
With Sheets("Archive")
Do While .Range("L" & RowCount) < ""
.Range("A" & RowCount & ":P" & 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" wrote:

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:P") 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!

Move Range with a date to a Matching Monthly Worksheet
 

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

--
Dan

On Jan 10, 1:29*pm, RyanH wrote:
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 & ":P" & 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" wrote:
Sub movebydate()

RowCount = 1
With Sheets("Archive")
* *Do While .Range("L" & RowCount) < ""
* * * .Range("A" & RowCount & ":P" & 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" wrote:

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:P") 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!- Hide quoted text -

- Show quoted text -