0

I am trying to copy several data from a table on a different sheet, to a new table on another sheet. My structure is like:

enter image description here

These are the steps that me and Mr. @QHarr have tried:

  1. Checked the objects and values exist
  2. Tried running the codes line by line
  3. Activate sheets and re-arranging the codes

None worked so far:

enter image description here

Here is my current codes:

Private Sub cmdedit_Click()
If MsgBox("Transfer selected asset to " & Me.ComboBox1.Text & "?", vbYesNo, "CONFIRMATION") = vbYes Then

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim wsendRow As Range
Dim wsendRow1 As Range
Dim lo As ListObject
Dim lr As ListRow

Set ws = Sheets("FIELD OFFICE DATABASE")
Set ws1 = Sheets("Transferred Items")
Set lo = ws1.ListObjects("table3")
Set lr = lo.ListRows.Add

Set wsendRow = ws.Range("B" & Rows.Count).End(xlUp)
'Set wsendRow1 = ws1.Range("A" & Rows.Count).End(xlUp)

ws.Activate

Range("B2").Select

Do Until ActiveCell.Address = wsendRow.Address

If ActiveCell.Value = Me.cmbemn.Text Then
'ws1.Unprotect "321321"
'ws1.Activate
lr.Range(1, 1).Value = Me.cmbemn.Text 'error appears on this line. if I place a comment here, the error will just move on the next line.
lr.Range(1, 2).Value = Me.TextBox1.Text
lr.Range(1, 3).Value = Me.txttype.Text
lr.Range(1, 4).Value = Me.txtmodel.Text
lr.Range(1, 5).Value = ActiveCell.Offset(0, 4).Value
lr.Range(1, 6).Value = ActiveCell.Offset(0, 5).Value
lr.Range(1, 7).Value = Me.txtpurdate.Text
lr.Range(1, 8).Value = Me.txtprice.Text
lr.Range(1, 9).Value = Me.txtcon.Text
lr.Range(1, 10).Value = ActiveCell.Offset(0, 9).Value
lr.Range(1, 11).Value = ActiveCell.Offset(0, 11).Value
lr.Range(1, 12).Value = Me.ComboBox1.Text
lr.Range(1, 13).Value = ActiveCell.Offset(0, 13).Value
lr.Range(1, 14).Value = Date
lr.Range(1, 15).Value = ws.Range("A13").Value
lr.Range(1, 16).Value = Me.TextBox2.Text
Exit Do
Exit Sub
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End If
End Sub

Codes never worked. Also to note, this question is in reference to my other question, (which took a lot of comments from me and Mr. QHarr) until he suggested that I should ask another question instead.

I hope someone can help me figure this out.

Thank you so much in advance

Vityata
  • 42,633
  • 8
  • 55
  • 100
Kelvs
  • 97
  • 2
  • 14
  • Have you tried looking here: [https://stackoverflow.com/questions/17980854/vba-runtime-error-1004-application-defined-or-object-defined-error-when-select](https://stackoverflow.com/questions/17980854/vba-runtime-error-1004-application-defined-or-object-defined-error-when-select) – Matt Jan 30 '18 at 08:58
  • @Matt, yes, and also the one who's helping me, still cant figure it out. I've also deleted the whole sheet and created a new one. Still no luck – Kelvs Jan 30 '18 at 09:10
  • 1
    Have you checked the References? Sometimes a MISSING reference can cause odd behaviour! Tools>References – MiguelH Jan 30 '18 at 09:24
  • You need to do the unprotect **before** inserting the new `ListRow`. Do `ws1.Unprotect "321321"` before `Set lr = lo.ListRows.Add`. – Axel Richter Jan 30 '18 at 10:05
  • `lr.Range(1, 1).Value`?, is it possible the code thinks range should be cells.? Range() is normally indicated by `range("A1")` and cells uses, `cells(1,1)` – Davesexcel Jan 30 '18 at 12:30
  • @Davesexcel: `lr.Range(1, 1).Value` is the shorter form for `lr.Range.Item(1, 1).Value`. Both should work. I really suspect that it is a issue with protect/unprotect. In a protected sheet the new `ListRow` will be created but does not having any `Range`. So using the `Range` will fail later. – Axel Richter Jan 30 '18 at 12:43
  • @AxelRichter, Your suggestion fixed my problem. I didn't thought of that solution because I was informed that protecting the sheet this way "ws1.Protect password:="321321", UserInterfaceOnly:=true", I will be able to do anything to the sheet without un-protecting it. Anyways, thank you very much for your help! – Kelvs Jan 31 '18 at 02:11

0 Answers0