use active cell to determine range

[Gizmo]

I am using the following code to import record from another sheet so the
record can be modified. The user has to look at the "Records" sheet, find the
row number of the record they want to modify, move to the AddRecord sheet and
input the row number in cell "A41" and click on the ImportRecordBtn. I want
to have the user click on a record on the "Records" sheet and then click on
ImportRecordBtn on the same sheet and have the record move to the AddRecord
sheet.

Sub ImportRecordBtn()

'Import data on AddRecord Sheet based on data selected from Records Sheet
ActiveWorkbook.Unprotect Password:="xxx"
Sheets("Records").Unprotect Password:="xxx"
Dim LName As String
Dim LType As String
Dim LAffectedSystem As String
Dim LNote As String
Dim LHours As Integer
Dim LModule As String
Dim LSubAssy As String
Dim LComponent As String
Dim LWaferCount As String
Dim LToolType As String
Dim LSerNo As Integer
Dim LResolved As String
Dim LKeyword As String
Dim LDept As String
Dim LOwner As String
Dim LFTEs As Integer
Dim LRecordNo As String


Dim LRow As Long
Dim LFound As Boolean

LRow = Range(A41).Address


' Move to Record sheet and record values based on row selected.
'Sheets("Records").Activate
'Sheets("Records").Unprotect Password:="xxx"


LName = Range("C" & LRow).Value
LNote = Range("D" & LRow).Value
LOwner = Range("E" & LRow).Value
LDept = Range("F" & LRow).Value
LType = Range("H" & LRow).Value
Range("I" & LRow).Value = "Yes"
Range("I" & LRow).Font.ColorIndex = 3
LToolType = Range("J" & LRow).Value
LSerNo = Range("K" & LRow).Value
LAffected = Range("L" & LRow).Value
LModule = Range("M" & LRow).Value
LSubAssy = Range("N" & LRow).Value
LComponent = Range("O" & LRow).Value
LHours = Range("P" & LRow).Value
LWaferCount = Range("Q" & LRow).Value
LKeyword = Range("R" & LRow).Value
LFTEs = Range("T" & LRow).Value
LRecordNo = Range("U" & LRow).Value


Sheets("Records").Protect Password:="xxx"

' Move to AddRecords sheet and input data based on Records sheet and Row
selected
Sheets("AddRecords").Visible = True
Sheets("Records").Visible = False
Sheets("AddRecords").Activate
Sheets("AddRecords").Unprotect Password:="xxx"

Range("B4").Value = LName
Range("C4").Value = LToolType
Range("D4").Value = LSerNo
Range("E4").Value = LWaferCount
Range("A8").Value = LType
Range("B8").Value = LAffected
Range("C8").Value = LModule
Range("D8").Value = LSubAssy
Range("E8").Value = LComponent
Range("A12").Value = LKeyword
Range("B12").Value = LNote
Range("E12").Value = LHours
Range("F12").Value = LFTEs
Range("G12").Value = "No"
Range("H5").Value = LDept
Range("I5").Value = LOwner
Range("A19").Value = LRecordNo

'Clear entries from cells
Range("E12").Value = ""

Sheets("AddRecords").Protect Password:="xxx"
ActiveWorkbook.Protect Password:="xxx"

End Sub

 

[Dave Peterson]

As long as the button is on the Records sheet, I think you could get away with
doing this:

LRow = Range("A41").Value

But maybe you could just use the row with the activecell???

LRow = activecell.row

 

[Gizmo]

Dave,

Thank you very much.

LRow = activecell.row did the trick.

I've been trying all sorts of combinations including
LRow=Range("A41").Value and nothing worked.

Thanks again.