PDA

View Full Version : [SOLVED] Extracting values from sheet 1 to sheet 2



exele
08-03-2014, 10:47 PM
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
08-04-2014, 06:15 AM
You have no reference to SHEET1.

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

Rob342
08-04-2014, 07:11 AM
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
08-04-2014, 07:42 AM
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
08-05-2014, 01:00 AM
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
08-05-2014, 01:33 AM
Because VBA assumes the activesheet.