Getting value after finding data VA

Question

I am looking a way to get value after finding it whenever I push a button, I have been using cells(rows.count,1).value and so on But still didn't work
here is my code

Private Sub CopyNota_Click()

On Error GoTo errorhandler:
Application.ScreenUpdating = False

Dim strpath As String
Dim copysheet As Worksheet
Dim pastesheet As Worksheet

Set copysheet = Worksheets("sheet3")
Set pastesheet = Worksheets("sheet5")
strpath = "E:\b\"
Filename = Dir(strpath & "b.xlsx")

If IsEmpty(Range("B2")) Then
    Workbooks("b.xlsx").Worksheets("sheet3").Range("H2").Copy destination:=Range("B2")
    Workbooks("b.xlsx").Worksheets("sheet3").Range("I2").Copy destination:=Range("C2")
    Workbooks("b.xlsx").Worksheets("sheet3").Range("J2").Copy destination:=Range("D2")
    Workbooks("b.xlsx").Save
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
Else
    Workbooks("b.xlsx").Worksheets("sheet3").Range("H2").Copy
    Worksheets("sheet5").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Workbooks("b.xlsx").Worksheets("sheet3").Range("I2").Copy
    Worksheets("sheet5").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Workbooks("b.xlsx").Worksheets("sheet3").Range("J2").Copy
    Worksheets("sheet5").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Workbooks("b.xlsx").Worksheets("sheet3").Range("A2").Value = Worksheets("sheet5").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value
End If

errorhandler:
If Err.Number = "52" Then
    MsgBox "Open The Workbooks First!!!"
    Exit Sub
End If

End Sub

Would anyone lend me a hand about my problems?

Thanks in advance

`Cells(Rows.Count,1)` looks like a lousy attempt at finding the last value cell in the column *A*. I recommend [Fionnuala's method](http://stackoverflow.com/a/71310/3819867) instead. In your case it's `Cells.Range("A:A").Find`. — user3819867, Mar 13 '15 at 07:50
@user381967 `Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)` is in fact how you find the first empty row in a column — Davesexcel, Mar 13 '15 at 08:19
@uniks Please explain what is not working, I am not sure why you couldn't copy all three cells and paste it Just once, instead of doing it 3 times. — Davesexcel, Mar 13 '15 at 08:25
OP, you could and should provide more info why does it not work, where the errors are and maybe add some comments to the code, so it is more clear, what are you trying to do. In this case, it was fairly easy, but as a general rule. — kolcinx, Mar 13 '15 at 11:08
sorry for replying too long for this post.I will explain the situation. I have two workbooks one named a.xlsm and the others is b.xlsx. File b.xlsx located in different folder named "b". What i am trying to do is checking cell A2 in a.xlsm IF IT'S EMPTY and then copy the value from cell H2:J2 in b.xlsx into cell B2:D2 in a.xlsm and move to the next cell IF NOT. After that i want to get the value from cell A2 in a.xlsm and copy it into A2 in b.xlsx and change it every time i push the button and that is the one that didn't work. Thanks — uniks, Mar 16 '15 at 05:25

score 0 · Answer 1 · answered Mar 13 '15 at 11:05

Try this one :

Private Sub CopyNota_Click()

On Error GoTo errorhandler:
Application.ScreenUpdating = False

Dim copysheet As Worksheet, pastesheet As Worksheet
Dim wbk As Workbook
Dim bolDoNotOpen As Boolean

Filename = "E:\b\b.xlsx"
'check if any of the opened workbook name is equal to the "b.xlsx"
For Each wbk In Workbooks
    If wbk.Name = "b.xlsx" Then
        bolDoNotOpen = True
    End If
Next wbk

'if none of the workbooks name = "b.xlsx" , then the "b.xlsx" is not open, so we can open it.
If bolDoNotOpen = False Then
    Workbooks.Open Filename
End If

Set copysheet = Workbooks("b.xlsx").Worksheets("sheet3") 'added workbook reference
Set pastesheet = Workbooks("b.xlsx").Worksheets("sheet5") 'added workbook reference

If IsEmpty(pastesheet.Range("B2")) Then
    pastesheet.Range("B2:D2").Value = copysheet.Range("H2:J2").Value
    Workbooks("b.xlsx").Save
Else
'you can change this to do all the values at once. But only if you know, that their row will always be the same.
    pastesheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = copysheet.Range("H2")
    pastesheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = copysheet.Range("I2")
    pastesheet.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = copysheet.Range("J2")
    copysheet.Range("A2") = pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If

Application.ScreenUpdating = True

Exit Sub
'obsolete now, we have checked or opened the workbook at the beginning
errorhandler:
    If Err.Number = "52" Then
        MsgBox "Open The Workbooks First!!!"
        Exit Sub
    End If
End Sub

Getting value after finding data VA

1 Answers1