0

I have created a spreadsheet with a sheet 1 input table, and want to transfer/copy that data into a sheet 2 log table. The input table on sheet 1 will have an inspection date and an inspection name cells. What I am having an issue with is that I can get the first line of the log to input, but the 2nd line I get a "Run0time error '1004': Application-defined or object defined error". Not sure what to look at from here.

Here's my code (I know, it's stiff rough and needs to be cleaned up):

Private Sub Add_Click()
Dim InspectionDate As String, InspectionName As String
Dim LastRow As Long
Worksheets("sheet1").Select
InspectionDate = Range("B4")
InspectionName = Range("B5")
Worksheets("sheet2").Select
Worksheets("sheet2").Range("B3").Select

If Worksheets("sheet2").Range("B3").Offset(1, 0) <> "" Then
    Worksheets("sheet2").Range("B3").End(x1Down).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InspectionDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = InspectionName
Worksheets("sheet1").Select
Worksheets("sheet1").Range("B4:B5").ClearContents

End Sub
James C
  • 1

1 Answers1

0

Two main reasons why .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, etc. should be avoided

The reasons are explained in the second answer on that page.

I have tested the code below and it works for me.

I'm autistic; so sometimes I appear to school others, when I'm only trying to help.


    Option Explicit
    
    Private Sub Add_Click()
    
    Dim InspectionDate$, InspectionName$
    Dim LastRow&
    Dim WS As Worksheet, WS2 As Worksheet
    
    Set WS = Worksheets("sheet1")
    Set WS2 = Worksheets("sheet2")
    
    InspectionDate = WS.Range("B4")
    InspectionName = WS.Range("B5")
    
    LastRow = 3
    
    If WS2.Range("B" & LastRow + 1) <> "" Then
        LastRow = WS2.Range("B" & Rows.count - 1).End(xlUp).Row
    End If
    
    WS2.Cells(LastRow + 1, 2) = InspectionDate
    WS2.Cells(LastRow + 1, 3) = InspectionName
    WS.Range("B4:B5").ClearContents
    
    End Sub
Cyrus
  • 356
  • 3
  • 14
  • 1
    No problem, i completely understand, my son is autistic also, I appreciate all the input. – James C Sep 06 '22 at 11:38
  • Thanks brother James. If my answer has solved your problem, then please accept it as the correct answer ? You can do so by clicking the large "V" tick at the top of my answer. – Cyrus Sep 06 '22 at 11:48