Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving data from one sheet to another | Excel Discussion (Misc queries) | |||
Moving data from one sheet to another | Excel Discussion (Misc queries) | |||
Moving data from one sheet to another | Excel Discussion (Misc queries) | |||
moving data from one sheet to another | Excel Discussion (Misc queries) | |||
Moving Data from one sheet to another | Excel Programming |