Copy and paste from WB to another WB

P

Peter

I'm working is a spreadsheet that has a list of value. I take the value of a
cell, and do a find in another workbook If I find a match then I want to
grab the some of the cell in that row, and copy/paste next to the cell value
in the first book. If the cell in my first sheet range c1:c100 has a value
then I go over to my pricelist.xls find that match, and I'm wanting to copy
and paste those cells back into the first sheet next to C1 ,so it would be
D1, E1, F1 My debug print statement prints out the information I need in
the Immediate window, just need to paste it back in the sheet.

Here is what I've got so far.

Sub CellValue()
Dim x, y, z
Dim wbkthis As Workbook
Dim shtthis As Worksheet
Dim rngThis As Range
Dim rngFind As Range
Dim Workbook
Dim Worksheet
Dim Xobj
Dim SrcWrkBook As Workbook
Dim SrcWrkSheet As Worksheet
Dim SrcRange As Range

Set wbkthis = ThisWorkbook
Set shtthis = wbkthis.Worksheets("Quotes")
Set SrcWrkBook = Workbooks.Open("c:\pricelist.xls", True, True)
Set Xobj = ActiveWorkbook.Sheets("owssvr(1)")
Windows("pricelist.xls").Visible = False

Set SrcRange = Xobj.Range("B02", "B275") ' second workbook

For Each x In Range("c1:c100")
If Not IsEmpty(x) Then
z = x.Offset(0, 3).Value
With SrcRange
Set rngFind = .Find(z)
If Not rngFind Is Nothing Then
Debug.Print rngFind.Offset(0, 0).Value & " " & rngFind.Offset(0,
1).Value & " " & rngFind.Offset(0, 3).Value & " " & rngFind.Offset(0,
7).Value
End If
End With

End If
Next x

Workbooks("pricelist.xls").Close SaveChanges:=False
End Sub


In my debug print statment I have all in the cells that I need in my second
sheet, but I'm not sure on how to cut and paste them into the second one.
Any ideas??
Thanks
 
P

Per Jessen

Hi

Try this:

Set rngFind = .Find(z)
If Not rngFind Is Nothing Then
x.Offset(0, 1) = rngFind.Offset(0, 1).Value
x.Offset(0, 2) = rngFind.Offset(0, 3).Value
x.Offset(0, 3) = rngFind.Offset(0, 7).Value
End If

Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top