Lastrow issue

[erutherford]

Just when I thought I could write a little code! Why does the following code paste to row 268 instead of the last row?

Private Sub CommandButton1_Click()
  ' Get the worksheets
    Dim shRead1 As Worksheet
    Dim shRead2 As Worksheet
    Dim shWrite As Worksheet
    Dim Lastrow As Long
    
    
   
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
    
    Set shRead1 = ThisWorkbook.Worksheets("Personnel")
    Set shRead2 = ThisWorkbook.Worksheets("This_year")
    Set shWrite = ThisWorkbook.Worksheets("Hstry")
    
       
    ' This will copy the values only
    
    shRead1.Range("P3:P66").Copy
    shWrite.Range("A2" & Lastrow).PasteSpecial xlPasteValues
    shRead2.Range("A2:G65").Copy
    shWrite.Range("B2" & Lastrow).PasteSpecial xlPasteValues
    
MsgBox "done"

End Sub

 

[Joe4]

You need to remove the old row number (2) from your range references!

These references:
Range("A2" & Lastrow)
should look like this:
Range("A" & Lastrow)

Otherwise, you are tacking on 2 to the beginning of the row number (i.e. "68" becomes "268").

 

[erutherford]

You need to remove the old row number (2) from your range references!

These references:
Range("A2" & Lastrow)
should look like this:
Range("A" & Lastrow)

Otherwise, you are tacking on 2 to the beginning of the row number (i.e. "68" becomes "268").

Still starting at line 64?

Private Sub CommandButton1_Click()
  ' Get the worksheets
    Dim shRead1 As Worksheet
    Dim shRead2 As Worksheet
    Dim shWrite As Worksheet
    Dim Lastrow As Long
    
    
   
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
    
    Set shRead1 = ThisWorkbook.Worksheets("Personnel")
    Set shRead2 = ThisWorkbook.Worksheets("This_year")
    Set shWrite = ThisWorkbook.Worksheets("Hstry")
    
       
    ' This will copy the values only
    
    shRead1.Range("P3:P66").Copy
    shWrite.Range("A" & Lastrow).PasteSpecial xlPasteValues
    shRead2.Range("A2:G65").Copy
    shWrite.Range("B" & Lastrow).PasteSpecial xlPasteValues
    
MsgBox "done"

End Sub

 

[Joe4]

What line should it be starting at?
Note that with your LastRow calculation, it is finding the last row in column A with data.
So if it is pasting to row 64, it means that there is something in cell A64.

Perhaps it would be better if you describe your data structure (or better yet, post a sample), and tell us exactly what you want to happen (instead of us guessing).

 

[erutherford]

What line should it be starting at?
Note that with your LastRow calculation, it is finding the last row in column A with data.
So if it is pasting to row 64, it means that there is something in cell A64.

Perhaps it would be better if you describe your data structure (or better yet, post a sample), and tell us exactly what you want to happen (instead of us guessing).

There is a header at A1. So the last row would be A2. I deleted Col A then added back, suspecting that there might be something that I couldn't see. The data structure is very simple as the code shows. Copy the data the range on shRead1 and paste it in the next row available on shWrite. I also deleted the "Hstry" sheet and redid it, same results.

 

[Peter_SSs]

What sheet is the command button on?

There is a header at A1. So the last row would be A2. I deleted Col A then added back,

What sheet are you referring to here?

 

[erutherford]

What sheet is the command button on?


What sheet are you referring to here?

The command button WAS on shRead2. I move it to wrksheet where the data is pasted (shWrite) and that seem to fix the "Line 64" paste. With the command button on the sheet where the data is pasted it is working fine. Why would the command btn location matter as long as things are pointed in the right direction?

 

[erutherford]

This is the code for the command button on the sheet where the data is pasted (shWrite)

Private Sub CommandButton1_Click()
 'Get the worksheets
    Dim shRead1 As Worksheet
    Dim shRead2 As Worksheet
    Dim shWrite As Worksheet
    Dim Lastrow As Long
    
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
     
    Set shRead1 = ThisWorkbook.Worksheets("Personnel")
    Set shRead2 = ThisWorkbook.Worksheets("This_year")
    Set shWrite = ThisWorkbook.Worksheets("Hstry")
      
    ' This will copy the values only
    
    shRead1.Range("P3:P66").Copy 'Reads current year
    shWrite.Range("A" & Lastrow + 1).PasteSpecial xlPasteValues
    shRead2.Range("A2:G65").Copy
    shWrite.Range("B" & Lastrow + 1).PasteSpecial xlPasteValues
    
MsgBox "done"

End Sub

 

[Peter_SSs]

.. as long as things are pointed in the right direction?

The problem was that things were not pointed in the right direction

 Lastrow = Range("A" & Rows.Count).End(xlUp).Row

This line of code does not specify which worksheet to use to find the last row. The default is that it will use the active sheet. That is the one that the button is (or was) on.

I think that if you put the button back on the original sheet, but move and change the Lastrow line like this it should work.

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
 
Set shRead1 = ThisWorkbook.Worksheets("Personnel")
Set shRead2 = ThisWorkbook.Worksheets("This_year")
Set shWrite = ThisWorkbook.Worksheets("Hstry")

Lastrow = shWrite.Range("A" & Rows.Count).End(xlUp).Row

 

[erutherford]

Now it works. Does "Lastrow" have to go below because parameters are set yet?