Why is my variable losing its value?

[gbell3587]

Hi

Im constantly copying & pasting data from 1 sheet to another & then i need to find the last row & add 1 to it so when i copy/paste the next set of data, it starts from the next row however the variable lrow always returns 1, like it resets its value or never changes to the actual last row. Any ideas? Its driving me mad!

sum example()
dim lrow as long
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = True
'
    Sheets("MTB").Select
    Range("I6:J212").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
        MsgBox lrow 'at this point, lrow returns 1 (which is fine)
    Selection.PasteSpecial Paste:=xlPasteValues
 lrow = lrow + 1 'lrow now returns 2 (should be 213)
    MsgBox lrow
    Range("A" & lrow).Select
    MsgBox lrow
end sub

 

[johnnyL]

Works for me. lrow did not report 1.

 

[gbell3587]

Works for me. lrow did not report 1.

Hi

Im constantly copying & pasting data from 1 sheet to another & then i need to find the last row & add 1 to it so when i copy/paste the next set of data, it starts from the next row however the variable lrow always returns 1, like it resets its value or never changes to the actual last row. Any ideas? Its driving me mad!

sum example()
dim lrow as long
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = True
'
    Sheets("MTB").Select
    Range("I6:J212").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
        MsgBox lrow 'at this point, lrow returns 1 (which is fine)
    Selection.PasteSpecial Paste:=xlPasteValues
 lrow = lrow + 1 'lrow now returns 2 (should be 213)
    MsgBox lrow
    Range("A" & lrow).Select
    MsgBox lrow
end sub

I have gaps in column I & J, so there are a lot of blanks when it copies - do you think this is throwing something out?

Works for me. lrow did not report 1.

I have gaps in column I & J, do you think this is throwing something out? Ive tried this in a fresh sheet but i still cant get lrow above 2!

See image & new code

Sub example()
Dim lrow As Long
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = True
'
    Sheets("MTB").Select
    Range("a6:b212").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
        MsgBox lrow 'at this point, lrow returns 1 (which is fine)
    Selection.PasteSpecial Paste:=xlPasteValues
 lrow = lrow + 1 'lrow now returns 2 (should be 213)
    MsgBox lrow
    Range("A" & lrow).Select
    MsgBox lrow
End Sub

 

[johnnyL]

Based on the comments in your code, it appears that you do not understand.

        MsgBox lrow 'at this point, lrow returns 1 (which is fine)
    Selection.PasteSpecial Paste:=xlPasteValues
 lrow = lrow + 1 'lrow now returns 2 (should be 213)
    MsgBox lrow

The first msgbox, your comment says lrow = 1.
Two lines later you add 1 to lrow and expect it to = 213?

If you want to update lrow, you have to run the calculation again, lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row for example.

 

[My Aswer Is This]

Try doing it like this:

Sub My_Script()
'Modified  7/1/2022  12:56:53 PM  EDT
Application.ScreenUpdating = False
Dim lrow As Long
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("MTB").Range("I6:J212").Copy
Sheets("Sheet2").Range("A" & lrow).PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
End Sub