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

 

[Colo]

Hi,
How about using the ThisWorkbook property to indicate the sheet's parent?
Something like...

ThisWorkbook.Worksheets("lastrow").Activate

 

[Vigash]

Thank you so much for your reply. But still having the same issue.

ThisWorkbook.Worksheets("lastrow").Activate

 

[My Aswer Is This]

Thank you so much for your reply. But still having the same issue.

ThisWorkbook.Worksheets("lastrow").Activate

Do you really have a worksheet named "lastrow"

Try changing the name to "Alpha"

And the run the code

ThisWorkbook.Worksheets("Alpha").Activate

 

[My Aswer Is This]

A lot of users use activate and select a lot which is not a good way to write code.

When something like this is better:
Sheets("Alpha").Range("A1").Value="Yes"
Or
Sheets("Alpha").Range("A1").Value=Sheets("Bravo").Range("G1").value

See no selecting or activating here

 

[Vigash]

Hi Colo,

If i have same macro name in another inactive sheet will this code behave in this way? Kindly bare my stupidity.

Regards,
Vigash

 

[Vigash]

Actually this is triggering this macro. Kindly advice.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.ListObjects("StockOut").Range.Rows.Count) Is Nothing Then
Call lastrow
End If
End Sub

 

[Vigash]

A lot of users use activate and select a lot which is not a good way to write code.

When something like this is better:
Sheets("Alpha").Range("A1").Value="Yes"
Or
Sheets("Alpha").Range("A1").Value=Sheets("Bravo").Range("G1").value

See no selecting or activating here

Thank you . Yes I have the sheet name lastrow .. I have changed the sheet name to Alpha and modified code. Still getting same issue..

 

[My Aswer Is This]

Here you have lastrow
Call lastrow

Which means run a script named lastrow

And then you say you have a sheet named lastrow

This may be causing a problem

 

[Colo]

The code is a bit verbose, but I tried to make the code with the output target worksheet in a variable to make it clear. Please give this a try.
Also, if you have a procedure with the same name in an inactive workbook, better change the name of the procedure for avoiding mess.
And I'm wondering if the procedure is called from the Worksheet_Change event is placed on another workbook. Better include the module name in which the procedure is placed.
Cahneg Call lastrow to (such as) Call Module1.lastrow

Sub lastrow()
    Dim sh As Worksheet
    Dim lr As Long

    Set sh = Application.ThisWorkbook.Worksheets("lastrow")

    lr = Application.ThisWorkbook.Worksheets("Stock Out").ListObjects("Stockout").ListRows.Count

    If sh.Range("A2").Value <> "" And sh.Range("B2").Value = "" Then
        sh.Range("B2").Value = lr
    ElseIf sh.Range("A2").Value <> "" And sh.Range("B2").Value <> "" Then
        sh.Range("A2").Value = sh.Range("B2").Value
        sh.Range("B2").Value = lr
    End If
End Sub