THEJONLOPEZ
New Member
- Joined
- Nov 9, 2017
- Messages
- 13
I have a Macro code that runs smoothly on my first sheet "RA LOG" once the info is copied and pasted to "RECEIVING LOG" im having trouble getting the macro to cut and paste to "TROUBLESHOOT" sheet. Below is the code i currently have for my "RA log"
Code:
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
