Extracting values from sheet 1 to sheet 2

[exele]

Hi,

I found the code below from this forum. The thread was about extracting data from one sheet to another and the code should work so that when you click the commandbutton, the values from row 1 in sheet1 should be copied to the sheet2. My button is however copying only values of "0". So if I have in my sheet1 in cell A1 =H10 and in H10 is value 500, the code copies "0" instead of "500". On the previous thread there were no comments about any problems, so the code should work fine but it just isn't?

I also have another question - could this be modified so that the values could be located in row 10 for instance, instead of row1?

Private

Sub  CommandButton1_Click() 
     Dim Lastrow As Long   
     With Worksheets("Sheet2")      
         If .Range("A1").Value = "" Then    
            Lastrow = 1 
         Else   
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 
         End If
        Rows(1).Copy .Cells(Lastrow, "A") 
        .Rows(Lastrow).Value = .Rows(Lastrow).Value 
     End With 
End Sub

[ranman256]

You have no reference to SHEET1.

You may want it: sheets("sheet1").Rows(1).Copy .Cells(Lastrow, "A")

[Rob342]

Try This, alter you ranges to suit application

Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Application.ScreenUpdating = False
    
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
LastRow = sh1.Cells(Rows.Count, 1).End(xlUp).Row
With sh1
    .Range("A1:M1" & LastRow).Copy Destination:=sh2.Range("A1")
End With
Application.ScreenUpdating =True    
End Sub

[Bob Phillips]

It's copying the formula, which will get adjusted, so if you want values, try

Sub  CommandButton1_Click() 
    Dim Lastrow As Long 

    With Worksheets("Sheet2") 

        If .Range("A1").Value = "" Then 
            Lastrow = 10 
        Else 
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 
        End If 
        Rows(1).Copy 
        .Cells(Lastrow, "A").PasteSpecial Paste:=xlPasteValues
    End With 
End Sub

[exele]

Thank you all for your advice,

at least with the last code it's working fine. Somehow this didn't need any reference to sheet1, that is something I also wondered myself.

[Bob Phillips]

Because VBA assumes the activesheet.