use active cell to determine range

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

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 wrote:
>
> 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

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.

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>