Copy cell value from W/book2 into W/book1

[Guest]

Hi

Using Office 2003

I'm trying to find in W/book2, Sheet1 Column E the first row containing the
word Sale.
Then in Column A same row, copy cells date value and paste value into
W/book1 Sheet8 cell C19.

TIA

 

[Mike Fogleman]

Sub Test()
Dim wb1 As Workbook, wb2 As Workbook
Dim x As Integer
Dim findrng As Range

Set wb1 = Workbooks("Book1")
Set wb2 = Workbooks("Book2")
Set findrng = wb2.Range("E1:E65536")
x = WorksheetFunction.Match("Sale", findrng, 0)
wb1.Worksheets("Sheet8").Range("C19").Value =
Range(findrng.Item(x).Address).Offset(, -4).Value
End Sub

Mike F

 

[Guest]

Many thanks Mike

Bob C

 

[Guest]

Mike
This is the code with correct W/book & sheet names inserted.
It is copying Cell A5 on the same sheet into C19.

Sub Test()

Dim wb1 As Workbook, wb2 As Workbook
Dim x As Integer
Dim findrng As Range

Set wb1 = Workbooks("Transactions.xls")
Set wb2 = Workbooks("Statements.xls")
Set findrng = wb2.Worksheets("Data").Range("E1:E65536")
x = WorksheetFunction.Match("Salary", findrng, 0)

wb1.Worksheets("Summary").Range("C19").Value = _
Range(findrng.Item(x).Address).Offset(0, -4).Value

End Sub

 

[Mike Fogleman]

Thanks for the update, glad it's working. As you can see, Copy/Paste is not
necessary to duplicate data. Just assign the value to the range. Reserve
Copy/Paste functions for transferring things like cell formatting or
filtered lists, etc.

Mike F

 

[Guest]

Mike it's not working.

It's not getting the offset value from Column A in wb2.
It just copies cell A5 in wb1 to cell c19 in the same wb1.
It's not referencing wb2 at all.

Sorry for my second post it looked OK but I had not tested.

Bob C
It cost's little to carry knowledge with you.

 

[Mike Fogleman]

You are right, I didn't test it either (shame on me). I didn't qualify wb2
as the source:

Sub Test()

Dim wb1 As Workbook, wb2 As Workbook
Dim x As Integer
Dim findrng As Range

Set wb1 = Workbooks("Transactions.xls")
Set wb2 = Workbooks("Statements.xls")
Set findrng = wb2.Worksheets("Data").Range("E1:E65536")
x = WorksheetFunction.Match("Salary", findrng, 0)

wb1.Worksheets("Summary").Range("C19").Value = _
wb2.Range(findrng.Item(x).Address).Offset(0, -4).Value

End Sub

Mike F

 

[Guest]

Mike
this part of code receives error "438 Object doesn't support this property
or method"

wb1.Worksheets("Summary").Range("C19").Value = _
wb2.Range(findrng.Item(x).Address).Offset(0, -4).Value

Bob C
It cost's little to carry knowledge with you.

 

[Guest]

Mike

Got it to work by changing code to
Sub Test()

Dim wb1 As Workbook
Dim ws2 As Worksheet ' change
Dim x As Integer
Dim findrng As Range

Set wb1 = Workbooks("Transaction.xls")
Set ws2 = Workbooks("Statements.xls").Worksheets("Data") ' change
Set findrng = ws2.Range("E5:E65536")
' change
x = WorksheetFunction.Match("Salary", findrng, 0)

wb1.Worksheets("Summary").Range("C19").Value = _
ws2.Range(findrng.Item(x).Address).Offset(0, -4).Value ' change

End Sub