Copy and paste from WB to another WB
 

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

Copy and paste from WB to another WB
 

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

On 1 Apr., 20:39, "Peter" wrote:
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