Moving row data to another sheet

  • Thread starter Thread starter RickK
  • Start date Start date
R

RickK

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.
 
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.
 
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
 
Back
Top