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
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