Runtime error copying value from one worksheet to another in same workbook

[mykal66]

Hey everyone.

I often use a form to collect data then write it to the next free row on a summary worksheet without issue. I've just set up a new form used a basic worksheet formatted to look like a form to collect the data but when i try to copy i keep getting a run-time error. Can anyone tell me what i am doing wrong please?

This is the code i have always used to copy form data to worksheet. The comented out line is how i've tried to adjust the code to copy from worksheet rather than form.

Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim strFileName As String
Dim dDate As Date
Set ws = Worksheets("Summary")
With ws
.Cells(lRow, 1).Value = Me.txt1.Value
  ‘.Cells(lRow, 1).Value = Sheets("Form").Range("O6").Value
  ActiveWorkbook.Save
'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
End With
End Sub

[patel]

lRow is not defined in the line
.Cells(lRow, 1).Value = Me.txt1.Value

[D_Marcel]

Just complementing, you need to define the value of the variable lRow before of this row highlighted by patel. Otherwise, your code runs this:

[VBA].Cells(0, 1).Value = Me.txt1.Value[/VBA]

And there's no row 0.
If you're running this code in a Module, use:

[VBA].Cells(lRow, 1).Value = ActiveSheet.Txt.Value[/VBA]

[mykal66]

Hi there.

Thank you both but i think I've put the wrong point across in my original post/ code

The line below is my original code which works when i use a form to collect the data

.Cells(lRow, 1).Value = Me.txt1.Value

But this time i am collecting from another worksheet and tried to amend the above line as below which is causing the error

 .Cells(lRow, 1).Value = Sheets("Form").Range("O6").Value

Basically i am trying to take the value of a specific cell on one worksheet, then copy it to the first available empty row/column 1 in another, the next line would repeat but place the copied text in empty row / column 2 etc.

[mykal66]

hi again. Not worry on this one as i am going to have to rethink altogether as it's merged cells that are causing the issue - thank you both again anyway