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]

I have found the issue. Thank you so much for your support ..
Its my stupidity.. Sorry for the inconvenience.
Issue is I am triggering another macro if any changes happen in lastrow sheet. So its creating new sheet.
If i disable the trigger I am getting the output in the desired sheet as I want.

Thank you so much @Colo & @MY Answer is this

Regards,
Vigash

 

[My Aswer Is This]

I have found the issue. Thank you so much for your support ..
Its my stupidity.. Sorry for the inconvenience.
Issue is I am triggering another macro if any changes happen in lastrow sheet. So its creating new sheet.
If i disable the trigger I am getting the output in the desired sheet as I want.

Thank you so much @Colo & @MY Answer is this

Regards,
Vigash

Glad to see you have it sorted out

 

[Colo]

Thank you so much @Colo & @MY Answer is this

I am glad you could find out what the bottleneck was.

 

[Vigash]

Thank you, Col & My Answer is this...
The following code is working but it's pasting formula as well , how to add paste special as Values ?

Usually, I do Selection.Paste special XltypeValues, I learned to a short bunch of code to a single line as follows
Sheets("Replenishment").Range("E6 : Q64").copy Sheets("Reorder").Range("B3")

How to paste as the value in the above-mentioned code..Kindly advice.

 

[Colo]

Hi,

When you use the PasteSpecial method, 2 lines are necessary.

Sheets("Replenishment").Range("E6:Q64").Copy
Sheets("Reorder").Range("B3").PasteSpecial Paste:=xlPasteValues

Copy method can be written in a single line as you did.

 

[My Aswer Is This]

Try this

Sub My_Script()
'Modified 8/29/2022  2:40:42 AM  EDT
Sheets("Replenishment").Range("E6 : Q64").Copy
Sheets("Reorder").Range("B3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Vigash]

Hi,

When you use the PasteSpecial method, 2 lines are necessary.

Sheets("Replenishment").Range("E6:Q64").Copy
Sheets("Reorder").Range("B3").PasteSpecial Paste:=xlPasteValues

Copy method can be written in a single line as you did.

Thank u So much Colo... Really you made my day...

 

[Vigash]

Try this

Sub My_Script()
'Modified 8/29/2022  2:40:42 AM  EDT
Sheets("Replenishment").Range("E6 : Q64").Copy
Sheets("Reorder").Range("B3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Thank you so much My Answer is this...

 

[Vigash]

Hi ,

I have a table connected to Mysql whenever last row count changes(Whenever new data addded) it should trigger the lastrow macro.

Kindly advice better method to solve this.

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

Regards,
Vigash

 

[My Aswer Is This]

Hi ,

I have a table connected to Mysql whenever last row count changes(Whenever new data addded) it should trigger the lastrow macro.

Kindly advice better method to solve this.

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

Regards,
Vigash

Is there a question here?