Thread: Selecting a number of single cells run time error

Morning All

I am trying to select specific cells and transfer this data into a new row in a different worksheet.
The code i am using i used sucessfully on a user form to take the entries from text boxes and place in a row in a new worksheet. I would like to try and do the same thing but on a worksheet rather than a user form.

My code is

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Template")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a policy number
If Trim(PolNum.Value) = "" Then
  PolNum.SetFocus
  MsgBox "Please enter a policy number"
  Exit Sub
'copy the data to the database

ws.Cells(iRow, 1).Value = PolNum.Value

'clear the data
PolNum.Value = ""

End If
End Sub

PolNum is a named cell, one of many i hope to just continue the cell names under one another. but i am getting an error. When i used this code in a user form the value for each range was me.name!.value and to clear was Me.Name!.Value = ""

Am i making a simple mistake or is this not possible to use through a worksheet based form?

The data entry sheet is called 'Data' and the blank sheet to copy to is 'template'.

Nick

Hello Nick,

To access a range that is a named range, you must enclose the name in quotes and use it as the index for a Range object, like this...

  ws.Cells(i, Row).Value = Range("PolNum").Value

  Range("PolNum").Value = ""