Transferring row details from one sheet to another from an action

[THEJONLOPEZ]

I know excel basics and am trying to learn more advanced options. I am trying to figure out how to transfer row information from one sheet into another sheet with similar criteria. The sheets act as steps in a process for my department. Is this achievable? I also would like to know how to create a drop down box containing text that correlates what action the row should take. Once the action is selected the information from one row will be transferred to another sheet in the process system. Also if this is possible to do and still re-sort the sheets without compromising the code. If anyone could help i would be very grateful.

 

[My Aswer Is This]

Sure things like this can be done.
Give us more details. Tell me what you wnat to do.
When you do this do this. Give exact details like sheet names columns and etc.
Do not say other cell to other worksheet or other vague terms.

Say something like: If Range "A1" on sheet named "Master" equals "Soup" then copy row (1) of sheet named "Master" to first empty row of sheet named "Soup"

I know excel basics and am trying to learn more advanced options. I am trying to figure out how to transfer row information from one sheet into another sheet with similar criteria. The sheets act as steps in a process for my department. Is this achievable? I also would like to know how to create a drop down box containing text that correlates what action the row should take. Once the action is selected the information from one row will be transferred to another sheet in the process system. Also if this is possible to do and still re-sort the sheets without compromising the code. If anyone could help i would be very grateful.

 

[THEJONLOPEZ]

I can provide this but maybe it would be more informative if I were to provide you with an example of the workbook? I could do this via email or post a dropbox link here?

 

[My Aswer Is This]

No. I never click on links. I feel it's best for you to explain in words. Just looking at a Workbook would not help.

I can provide this but maybe it would be more informative if I were to provide you with an example of the workbook? I could do this via email or post a dropbox link here?

 

[THEJONLOPEZ]

No. I never click on links. I feel it's best for you to explain in words. Just looking at a Workbook would not help.

I can respect that. First sheet "RA LOG" range A2:H1000 must be locked and nontransferable to other sheets. Each record in this range must copy and paste through an action which has this show up on sheet 2 "RECEIVING" Once on this sheet each record in range A6:E260 must work independently from one another and transfer based on the action given. The whole record must be cut and copied when the action is performed. This range is the same for all the other sheets and should work the same. Sheet 3 is "TROUBLESHOOT" sheet 4 is "AWAITING APPROVAL" sheet 5 is "IN REPAIR" sheet 6 is "QC TESTING" sheet 7 is "READY TO SHIP". All this needs to be reversible to make sure mistakes aren't made. The first sheet is the only sheet that needs to stay absolute throughout the whole process and nit changing from the original input. The other sheets however just represent position and location for clarification and efficiency. Please let me know what you can do! THANKS!

 

[THEJONLOPEZ]

No. I never click on links. I feel it's best for you to explain in words. Just looking at a Workbook would not help.

"My answer is this" please review this code i currently have for my first sheet. I am trying to use this same code on a different sheet with different ranges can you please tell me if there is a simpler way of doing this. I just need it to cut and paste to the next sheet once i hit the activex button with a key word in the column.

Option Explicit


Public Sub ra_log_execute()
' Performs an action on a row based on the command on column I of that row
' Commands
' RECEIVE - copies data to "Receiving" worksheet
' UNDO - finds the data on the "Receiving" worksheet and clears it


' Change log
' 2017-11-20 - created by JAO


On Error GoTo error_handler


    ' Declarations
        Dim longRowCount, longRowCount2, longRowCount3 As Long  ' counters to tell us which row we're on
        Dim strUndoCell As Range                                ' cell used for Undo operation
        Dim strCmdCol As String                                 ' this is the column on the sheet where our commands are located
        Dim strRALogSheet, strReceivingSheet As String          ' the sheet names we are going to be working with
        Dim strData_Cust, strData_RA, strData_SN, strData_DateRec, strData_Comm As String   ' data values we're copying or clearing
        Dim strRA_Cust_Col, strRA_RA_Col, strRA_SN_Col, strRA_DateRec_Col As String         ' Column IDs on the Receiving sheet
        Dim strREC_Cust_Col, strREC_RA_Col, strREC_SN_Col, strREC_DateRec_Col As String     ' Column IDs on the Receiving sheet
        Dim strCommand As String                                ' Command to be executed (e.g. Rec, Undo, etc.)




    ' Initialize
        strCmdCol = "I"                     ' column where our commands are
        strRALogSheet = "RA Log"            ' name of RA Log sheet
        strReceivingSheet = "Receiving"     ' name of Receiving sheet
        strRA_Cust_Col = "C"                ' on the RA Log sheet, the Customer column
        strRA_RA_Col = "A"                  ' on the RA Log sheet, the RA number column
        strRA_SN_Col = "E"                  ' on the RA Log sheet, the Serial # column
        strRA_DateRec_Col = "G"             ' on the RA Log sheet, the Date Received column
        strREC_Cust_Col = "A"               ' on the Receiving sheet, the Customer column
        strREC_RA_Col = "B"                 ' on the Receiving sheet, the RA number column
        strREC_SN_Col = "C"                 ' on the Receiving sheet, the Serial # column
        strREC_DateRec_Col = "D"            ' on the Receiving sheet, the Date Received column


    
    ' Procedure
        ' Start at home
            Sheets(strRALogSheet).Range("A1").Select
            
        ' Go down the list and work our commands
            For longRowCount = 2 To Selection.CurrentRegion.Rows.Count
                ' Evaluate the Command column in each row and perform the necessary task based on the command
                
                ' Our command is the value in whatever we said is the command column. We cast the value
                '   as a string and force it to lower case, which makes it more flexible for the person
                '   typing the commands
                strCommand = LCase(CStr(Sheets(strRALogSheet).Range(strCmdCol & longRowCount).Value))
            
                Select Case strCommand
                
                    Case "copied", "undone", "not on receiving sheet"
                        ' This is a relic from an earlier execution--clear it
                            Sheets(strRALogSheet).Range(strCmdCol & longRowCount).Value = ""
                    
                    Case "r", "rec", "receive", "received"
                        ' Copy the row's data from the log sheet
                                strData_Cust = Sheets(strRALogSheet).Range(strRA_Cust_Col & longRowCount).Value
                                strData_RA = Sheets(strRALogSheet).Range(strRA_RA_Col & longRowCount).Value
                                strData_SN = Sheets(strRALogSheet).Range(strRA_SN_Col & longRowCount).Value
                                strData_DateRec = Sheets(strRALogSheet).Range(strRA_DateRec_Col & longRowCount).Value
                                                       
                       ' Paste it to the receiving log sheet
                            ' first find a blank row
                                longRowCount2 = 5   ' this is the title row of data on that sheet
                                Do
                                    longRowCount2 = longRowCount2 + 1
                                Loop Until (Sheets(strReceivingSheet).Range(strREC_Cust_Col & longRowCount2).Value = "")
                            
                            ' now paste the data
                                Sheets(strReceivingSheet).Range(strREC_Cust_Col & longRowCount2).Value = strData_Cust
                                Sheets(strReceivingSheet).Range(strREC_RA_Col & longRowCount2).Value = strData_RA
                                Sheets(strReceivingSheet).Range(strREC_SN_Col & longRowCount2).Value = strData_SN
                                Sheets(strReceivingSheet).Range(strREC_DateRec_Col & longRowCount2).Value = strData_DateRec
                    
                        ' Change the command to show "Copied"
                            Sheets(strRALogSheet).Range(strCmdCol & longRowCount).Value = "Copied"
                                                
                    Case "u", "undo"
                        ' Look at the RA number and find it on the Receiving log page
                        strData_RA = Sheets(strRALogSheet).Range(strRA_RA_Col & longRowCount).Value ' value we're searching for
                        
                        Sheets(strReceivingSheet).Activate
                        
                        ActiveSheet.Cells.Find( _
                            What:=strData_RA, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchDirection:=xlNext).Activate
                                               
                        ActiveCell.EntireRow.Delete
                                   
                        Sheets(strRALogSheet).Activate
                        
                        ' Change the command to show "Undone"
                            Sheets(strRALogSheet).Range(strCmdCol & longRowCount).Value = "Undone"
                        
                    Case Else
                        ' skip and do nothing
                                                
                End Select
                
            Next longRowCount
    
    ' Done
Exit Sub


error_handler:
    Select Case Err.Number
        
        Case 91
            ' Couldn't find the RA number on the Receiving worksheet
                Sheets(strRALogSheet).Activate
                Sheets(strRALogSheet).Range(strCmdCol & longRowCount).Value = "Not on Receiving sheet"
        
        Case Else
            MsgBox _
                Prompt:="Error # " & Err.Number & ": " & Err.Description, _
                Title:="Error at Execute button", _
                Buttons:=vbOKCancel
                    
    End Select


End Sub


Private Sub cmdRA_Exec_Click()
    
    ra_log_execute
    
End Sub

 

[My Aswer Is This]

I'm sorry but this is way too much code for me to read and try and figure out what your wanting.
If you could provide in words what your wanting I may be able to help.

 

[THEJONLOPEZ]

Sheet 2 "RECEIVING" has information on each row from range A7:E260 column F will have a activeX click button that has a caption of execute. In this column if "R" "REC" "RECEIVE" is typed in caps or lower case, then hit the execute button and it transfers to the next available row in "TROUBLESHOOT" with the same range and columns. Which starts with "Customer" "RA" "Serial Number" "Date Received" "Comments." If need be on sheet 2 "RECEIVING" if "U" "Undo" is typed it undoes the previous command and brings the most recently cut and copied data back to its original position on the sheet.

 

[My Aswer Is This]

Trying to do a undo command is a very difficult thing to do.
And you said: I know excel basics and am trying to learn more advanced options

But putting a Activex button on every row and wanting to click this button is not a good way to do things.

Then you say:
In this column if "R" "REC" "RECEIVE" is typed in caps or lower case,

What does "R" "REC" "RECEIVED" mean?

Do you mean if "R" or "REC" or "RECEIVED" is entered do this?

Copy this entire row to sheet named "TROUBLESHOOT" first empty row
And each row will have data in column "A"
So we will look for lastrow with data in column A

This undo feature may be able to be done but lets figure out this first part first.

I would also suggest using a DataValidation list in column "F" so users must select "Received" and not be allowed to enter "R" or "REC" Or "RECEIVED" this is a sloppy way to do things.

And we can have this script run without any need for all these Activex controls.

When you enter the value in column "F" the script I can write will run automatically.

It's better to tell us what you want and let us figure out the best way to do it.
Your way of doing it with activex buttons would require a lot more work.

 

[THEJONLOPEZ]

Trying to do a undo command is a very difficult thing to do.
And you said: I know excel basics and am trying to learn more advanced options

But putting a Activex button on every row and wanting to click this button is not a good way to do things.

Then you say:
In this column if "R" "REC" "RECEIVE" is typed in caps or lower case,

What does "R" "REC" "RECEIVED" mean?

Do you mean if "R" or "REC" or "RECEIVED" is entered do this?

Copy this entire row to sheet named "TROUBLESHOOT" first empty row
And each row will have data in column "A"
So we will look for lastrow with data in column A

This undo feature may be able to be done but lets figure out this first part first.

I would also suggest using a DataValidation list in column "F" so users must select "Received" and not be allowed to enter "R" or "REC" Or "RECEIVED" this is a sloppy way to do things.

And we can have this script run without any need for all these Activex controls.

When you enter the value in column "F" the script I can write will run automatically.

It's better to tell us what you want and let us figure out the best way to do it.
Your way of doing it with activex buttons would require a lot more work.

Correct. The way you stated of having a DataValidation list in column F would be an easier method of doing things which helps to eliminate user error. Is this something you can do? I am still trying to see what is the best way. It seems the way you stated of datavalidation list in column F to run the code automatically when "received" is chosen may be easiest. So if we can move on with this method that would be much appreciated.