CODEWITHSUNDEEP
vba
Tzhoji
Tzhoji

Reputation: 11

Find value in other sheet of other workbook and copy to the sheet of current work book

I have 2 sheets in 2 different work book. a) Workbook(A).Sheets("A") b)Workbook(B).Sheets("B")

Sheet A in workbook A is destination file. Sheet B in Workbook B is source file.

User Input file name of B( to open sheet B) in sheet A, then from sheet B, it loops to find values which are larger than a reference value. With found value, the value will be copied and pasted to sheet A(workbook b) instantaneously . Then loop back in sheet B to find subsequent value(which is larger than reference value). The process continues until it meets certain criteria.

I manage to find the value in loop, but i have to paste the value into sheet B, then copy to sheet A. For sure, user has to click the message whether to save sheet B. Below is the code written. Pls enlighten me.

Dim VerR As Workbook
Dim VerRDest As Worksheet
Dim VerRMaxR As Range

Application.ScreenUpdating = False
XLS = Cells(16, 5)
Workbooks(XLS).Activate

Sheets("ROLLER").Select
Path = Cells(18, 5)
NoR = Cells(17, 5)
Workbooks.Open Filename:=Path

Set VerR = Workbooks(XLS)
Sheets("Stat_1").Select        
y = 3
i = 4
Do While (Cells(i, 1) <> "")
    i = i + 1
Loop

m2 = i - 1
For x = 4 To m2
    If (cells(x,2)>5) Then
        y = y + 1
        TF = True
        If TF Then
            Cells(y, 6) = Cells(x + 1, 1)
            Cells(y, 5) = Cells(x + 1, 2)
        End If
    End If
Next x

Set VerMax1 = ActiveSheet.Range(Cells(4, 5), Cells(y, 6))
VerMax1.Copy
ActiveWorkbook.Close

Set VerRDest = VerR.Worksheets("ROLLER")
Set VerRMaxR = VerRDest.Range(Cells(4, 3), Cells(y, 4))
VerRMaxR.PasteSpecial , Paste:=xlPasteValues

My objective is to paste the value without affecting source workbook.Pls help. Thanks.I truly appreciate your idea.I am at my wits end.

Upvotes: 0

Views: 2063

Answers (1)

Jon49
Jon49

Reputation: 4606

dim r1 as range, r2 as range

r1.copy r2

or 

r1.copy
r2.pastespecial ....

Upvotes: 1

Related Questions