VBA entry in last row

[Mohsin110]

Hi, Please review my small code, is there any problem, i just want to enter two values to second sheet from 1st sheet. but it's not working with following code. sharing just part of code.

Sub SaveButton()
    Dim sh, sh2, sh3 As Worksheet
    Dim LR, LRB, ID, l, i As Long

    Set sh = ThisWorkbook.Sheets("MRN")              'Sheet 1
    Set sh2 = ThisWorkbook.Sheets("mrnData")       'Sheet 2
    Set sh3 = ThisWorkbook.Sheets("SubmitCS")     'Sheet 3

    LR = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1    'Last row of sheet 2
    LRB = sh3.Cells(Rows.Count, 1).End(xlUp).Row + 1  'Last row of sheet 3

    For i = 8 To LRB    'entering to last empty row starting from 8th row
        With sh3
           .Cells(LRB, 1) = Range("Date").Value
           .Cells(LRB, 2) = Range("ID").Value
        End With
     Next i

 End Sub

 

[Georgiboy]

I think in the bit below, you could do with swapping LRB for i:

        With sh3
           .Cells(LRB, 1) = Range("Date").Value
           .Cells(LRB, 2) = Range("ID").Value
        End With

You could also do it without a loop:

    With sh3
       .Range(.Cells(8, 1), .Cells(LRB, 1)) = Range("Date").Value
       .Range(.Cells(8, 2), .Cells(LRB, 2)) = Range("ID").Value
    End With

 

[MARK858]

It won't cause an error (normally) but also your declarations are wrong for VBA if you want all of the lines below to be worksheets

Dim sh, sh2, sh3 As Worksheet

and all the below to be Longs

Dim LR, LRB, ID, l, i As Long

You need to explicitly declare each Variable in VBA or else they are Variants i.e. it should be

    Dim sh As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Dim LR As Long, LRB As Long, ID As Long, l As Long, i As Long

 

[Mohsin110]

I think in the bit below, you could do with swapping LRB for i:

        With sh3
           .Cells(LRB, 1) = Range("Date").Value
           .Cells(LRB, 2) = Range("ID").Value
        End With

You could also do it without a loop:

    With sh3
       .Range(.Cells(8, 1), .Cells(LRB, 1)) = Range("Date").Value
       .Range(.Cells(8, 2), .Cells(LRB, 2)) = Range("ID").Value
    End With

Thanks for guiding me, i swapped LRB with i as you suggest, but it's entering data to all rows each time, not just in one last row. Please check.

  For i = 8 To LRB
        With sh3
           .Cells(i, 1) = Range("Date").Value
           .Cells(i, 2) = Range("ID").Value + 1
        End With
  Next i

 

[Georgiboy]

Yes sorry that is what i thought you wanted to do.

In the OP it states "two values to second sheet from 1st sheet" but your code seems to set up to move values to the third sheet, could that be the issue?

I just tested the below (with MARK858's changes) and it seems to function correctly:

Sub SaveButton()
    Dim sh2 As Worksheet, sh3 As Worksheet
    Dim LRB As Long, ID As Long

    Set sh2 = ThisWorkbook.Sheets("mrnData")       'Sheet 2
    Set sh3 = ThisWorkbook.Sheets("SubmitCS")     'Sheet 3

    LRB = sh3.Cells(Rows.Count, 1).End(xlUp).Row + 1  'Last row of sheet 3

    With sh3
       .Cells(LRB, 1) = Range("Date").Value
       .Cells(LRB, 2) = Range("ID").Value + 1
    End With
 End Sub

 

[Mohsin110]

Yes sorry that is what i thought you wanted to do.

In the OP it states "two values to second sheet from 1st sheet" but your code seems to set up to move values to the third sheet, could that be the issue?

I just tested the below (with MARK858's changes) and it seems to function correctly:

Sub SaveButton()
    Dim sh2 As Worksheet, sh3 As Worksheet
    Dim LRB As Long, ID As Long

    Set sh2 = ThisWorkbook.Sheets("mrnData")       'Sheet 2
    Set sh3 = ThisWorkbook.Sheets("SubmitCS")     'Sheet 3

    LRB = sh3.Cells(Rows.Count, 1).End(xlUp).Row + 1  'Last row of sheet 3

    With sh3
       .Cells(LRB, 1) = Range("Date").Value
       .Cells(LRB, 2) = Range("ID").Value + 1
    End With
 End Sub

It's working now, thanks for solving my problem.