Moving row data to another sheet
 

I have a "sales forecast" spreadsheet, in which data is
changes from a forecast to completed.
Basicly what I would like to do is instead of our sales
people having to cut and paste the data (from one month to
another month shhet), it could be done from vba code.
The citeria would be that the row data changes (from
forecast to actual)when the user chooses from a cell
(column M) the month the order closed (months are in a
validation list). So he/she might show a project forecast
to possibly run in June and places the info in the June
sheet, the project closes in July. The use could just cut
and paste this row's info (column A to col P) from the
June sheet to the July sheet. But I'd rather have a cmd
button that goes through the sheet, finds the data and
places it into proper sheet (based upon month closed).
All help appreciated.

Moving row data to another sheet
 

A couple of things to do:

You will use the Change Event on either the workbook or worksheet, which
ever you prefer and depending on your situation. In this case, I'm assuming
you are going to use the Worksheet Change Event

Private Sub Worksheet_Change(ByVal Target as Range)
Dim CurRow as Long, PasteToRow as Long, PasteToMonth as String
Dim CurMonth as String
EnableEvents = False
If Target.Column = 12 Then
'Gets the Worksheet name of the active worksheet
CurMonth = Target.Parent.Name
PasteToMonth = UCase(Format(Target.Value,"MMM"))
If PasteToMonth < Target.Parent.Name Then
CurRow = Target.Row
Worksheets("CurMonth").Range("A" & CurRow & ":P" & CurRow).Copy
PasteToRow = Worksheets(PasteToMonth). _
Range("A" & HeaderRow).End(xlDown)
Worksheets(PasteToMonth).Paste
Destination:=Worksheets(PasteToMonth). _
Range("A" & PasteToRow & ":P" & PasteToRow)
Worksheets(CurMonth).Range("A" & CurRow & ":P" & CurRow). _
Delete(xlShiftUp)
End If
End If
EnableEvents = True
End Sub

Assumptions:

Each of your worksheets are named with the first 3 letter of the month in
all caps.
This is the behavior you want taken place the moment the user changes the
closed month on the project.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"RickK" wrote in message
...
I have a "sales forecast" spreadsheet, in which data is
changes from a forecast to completed.
Basicly what I would like to do is instead of our sales
people having to cut and paste the data (from one month to
another month shhet), it could be done from vba code.
The citeria would be that the row data changes (from
forecast to actual)when the user chooses from a cell
(column M) the month the order closed (months are in a
validation list). So he/she might show a project forecast
to possibly run in June and places the info in the June
sheet, the project closes in July. The use could just cut
and paste this row's info (column A to col P) from the
June sheet to the July sheet. But I'd rather have a cmd
button that goes through the sheet, finds the data and
places it into proper sheet (based upon month closed).
All help appreciated.

Moving row data to another sheet
 

Thankyou Ronald, I'll give this a try.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Moving row data to another sheet
 

The code, stops running at

Worksheets("CurMonth").Range("A" & CurRow & ":P" &
CurRow).Copy

And generates a "Subscript out of range" (run error 9)
message.
By the way I would want to copy from column A to Column Q.
In your orginal code please eplain the ":P" & CurRow
don't understand this.
Thnaks

-----Original Message-----
A couple of things to do:

You will use the Change Event on either the workbook or
worksheet, which
ever you prefer and depending on your situation. In this
case, I'm assuming
you are going to use the Worksheet Change Event

Private Sub Worksheet_Change(ByVal Target as Range)
Dim CurRow as Long, PasteToRow as Long, PasteToMonth
as String
Dim CurMonth as String
EnableEvents = False
If Target.Column = 12 Then
'Gets the Worksheet name of the active worksheet
CurMonth = Target.Parent.Name
PasteToMonth = UCase(Format(Target.Value,"MMM"))
If PasteToMonth < Target.Parent.Name Then
CurRow = Target.Row
Worksheets("CurMonth").Range("A" & CurRow
& ":P" & CurRow).Copy
PasteToRow = Worksheets(PasteToMonth). _
Range("A" & HeaderRow).End(xlDown)
Worksheets(PasteToMonth).Paste
Destination:=Worksheets(PasteToMonth). _
Range("A" & PasteToRow & ":P" & PasteToRow)
Worksheets(CurMonth).Range("A" & CurRow
& ":P" & CurRow). _
Delete(xlShiftUp)
End If
End If
EnableEvents = True
End Sub

Assumptions:

Each of your worksheets are named with the first 3 letter
of the month in
all caps.
This is the behavior you want taken place the moment the
user changes the
closed month on the project.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"RickK" wrote in message
...
I have a "sales forecast" spreadsheet, in which data is
changes from a forecast to completed.
Basicly what I would like to do is instead of our sales
people having to cut and paste the data (from one month
to
another month shhet), it could be done from vba code.
The citeria would be that the row data changes (from
forecast to actual)when the user chooses from a cell
(column M) the month the order closed (months are in a
validation list). So he/she might show a project
forecast
to possibly run in June and places the info in the June
sheet, the project closes in July. The use could just
cut
and paste this row's info (column A to col P) from the
June sheet to the July sheet. But I'd rather have a cmd
button that goes through the sheet, finds the data and
places it into proper sheet (based upon month closed).
All help appreciated.




.