Macro help needed!

[THEJONLOPEZ]

I am in need of formatting this macro to my other sheets in my workbook. However, the problem i am encountering is that the second sheet "receiving" that the row data is pasted to has different columns and i need this to cut and paste to the third sheet "troubleshoot" not copy and paste.

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

 

[THEJONLOPEZ]

This is what I have so far for this.

Option Explicit


Public Sub receiving_execute()
' Performs an action on a row based on the command on column I of that row
' Commands
' RECEIVE - cuts data to "Troubleshoot" worksheet
' UNDO - finds the data on the "Troubleshoot" 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 strReceivingSheet, strTroubleshootSheet 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 strREC_Cust_Col, strREC_RA_Col, strREC_SN_Col, strREC_DateRec_Col As String ' Column IDs on the Receiving sheet
Dim strTRO_Cust_Col, strTRO_RA_Col, strTRO_SN_Col, strTRO_DateRec_Col As String ' Column IDs on the Troubleshoot sheet
Dim strCommand As String ' Command to be executed (e.g. Rec, Undo, etc.)




' Initialize
strCmdCol = "F" ' column where our commands are
strReceivingSheet = "Receiving" ' name of Receiving sheet
strTroubleshootSheet = "Troubleshoot" ' name of Troubleshoot sheet
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
strTRO_Cust_Col = "A" ' on the Troubleshoot sheet, the Customer column
strTRO_RA_Col = "B" ' on the Troubleshoot sheet, the RA number column
strTRO_SN_Col = "C" ' on the Troubleshoot sheet, the Serial # column
strTRO_DateRec_Col = "D" ' on the Troubleshoot sheet, the Date Received column



' Procedure
' Start at home
Sheets(strReceivingSheet).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(strReceivingSheet).Range(strCmdCol & longRowCount).Value))

Select Case strCommand

Case "copied", "undone", "not on troubleshoot sheet"
' This is a relic from an earlier execution--clear it
Sheets(strReceivingSheet).Range(strCmdCol & longRowCount).Value = ""

Case "r", "rec", "receive", "received"
' Copy the row's data from the log sheet
strData_Cust = Sheets(strReceivingSheet).Range(strREC_Cust_Col & longRowCount).Value
strData_RA = Sheets(strReceivingSheet).Range(strREC_RA_Col & longRowCount).Value
strData_SN = Sheets(strReceivingSheet).Range(strREC_SN_Col & longRowCount).Value
strData_DateRec = Sheets(strReceivingSheet).Range(strREC_DateRec_Col & longRowCount).Value

' Paste it to the Troubleshoot 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(strTroubleshootSheet).Range(strTRO_Cust_Col & longRowCount2).Value = "")

' now paste the data
Sheets(strTroubleshootSheet).Range(strTRO_Cust_Col & longRowCount2).Value = strData_Cust
Sheets(strTroubleshootSheet).Range(strTRO_RA_Col & longRowCount2).Value = strData_RA
Sheets(strTroubleshootSheet).Range(strTRO_SN_Col & longRowCount2).Value = strData_SN
Sheets(strTroubleshootSheet).Range(strTRO_DateRec_Col & longRowCount2).Value = strData_DateRec

' Change the command to show "Copied"
Sheets(strReceivingSheet).Range(strCmdCol & longRowCount).Value = "Copied"

Case "u", "undo"
' Look at the RA number and find it on the Receiving log page
strData_RA = Sheets(strReceivingSheet).Range(strREC_RA_Col & longRowCount).Value ' value we're searching for

Sheets(strTroubleshootSheet).Activate

ActiveSheet.Cells.Find( _
What:=strData_RA, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchDirection:=xlNext).Activate

ActiveCell.EntireRow.Delete

Sheets(strReceivingSheet).Activate

' Change the command to show "Undone"
Sheets(strReceivingSheet).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 Troubleshoot worksheet
Sheets(strReceivingSheet).Activate
Sheets(strReceivingSheet).Range(strCmdCol & longRowCount).Value = "Not on Troubleshoot sheet"

Case Else
MsgBox _
Prompt:="Error # " & Err.Number & ": " & Err.Description, _
Title:="Error at Execute button", _
Buttons:=vbOKCancel

End Select


End Sub


Private Sub cmdREC_Exec_Click()

receiving_execute

End Sub