VBA code opening a new workbook and giving out put. how to fix it?

[Vigash]

Hi All,

This is the code I am trying to execute. I am getting the result correctly. But my issue is instead of lastrow sheet in the same workbook macro creates a new workbook with a new sheet named as the last row and giving output there.. Why it's not giving the result in the same worksheet (lastrow) which is available in the existing workbook? Please clarify.

Sub lastrowfinder()
Dim lr As Integer
Worksheets("Stock Out").Activate
ActiveSheet.ListObjects("Stockout").Range.Select
lr = ActiveSheet.ListObjects("Stockout").ListRows.Count
Worksheets("lastrow").Activate
Range("A2").Select
If Range("A2").Value = "" And Range("B2").Value = "" Then
Range("A2").Value = lr

ElseIf Range("A2").Value <> "" And Range("B2").Value = "" Then
Selection.Offset(0, 1).Value = lr

ElseIf Range("A2").Value <> "" And Range("B2").Value <> "" Then
Range("A2").Value = Range("B2").Value
Range("B2").Value = lr

End If
End Sub

Thanks,
Vigash

 

[Vigash]

Hi,

Sorry for the late .. I am trying to execute macro if any new data is added to my table.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Records As Range
Set Records = Range("Stockout")
If Not Application.Intersect(Records, Range(Target.Address)) Is Nothing Then

Call Module3.lastrow_value

End If

End Sub

I am struggling to work with events especially when it comes to the Table structure. Kindly advice.

Vigash