| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How format SUMIF formula for matching date range and column value? | Excel Worksheet Functions | |||
| On opening a worksheet move focus to a cell based on date | Excel Worksheet Functions | |||
| Find matching date in another worksheet, copy and paste data | Excel Discussion (Misc queries) | |||
| display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
| Find Matching Text In Col A move to Col B | Excel Worksheet Functions | |||
Linear Mode
