find value and paste contents of cells

[Guest]

Hi, Im hoping someone can help me.

I am trying to run a macro to copy cells A10:G10 from workbook1 and paste
them into workbook2.

straightforward enough there but i want the macro to first find a match for
the contents of A10 (workbook1) in column A of workbook2 then overwriting the
other cells B-G.

I hope the above makes sense, and apreciate any help you can offer.

 

[Dave Peterson]

Option Explicit
sub testme01()

dim wks1 as worksheet
dim wks2 as worksheet
dim res as variant

set wks1 = workbooks("book1.xls").worksheets("sheet1")
set wks2 = workbooks("book2.xls").worksheets("sheet2")

res = application.match(wks1.range("a10").value, wks2.range("a:a"),0)

if iserror(res) then
'no match!
else
wks1.range("B10:G10").copy _
destination:=wks2.range("a:a")(res).offset(0,1)
end if

end sub

 

[Guest]

Thanks Dave, ive used the code you provided and fiddled with it slightly
(changed file names and set it to open and close the 2nd worksheet, trouble
is its not working, none of the changes are being saved in workbook2
(qrylogmk2.xls). Also i have tried running it with an invalid number in cell
A14 and no error came up.

im very green when it comes to VB so have listed the code below incase ive
done something dumb (highly possible) or if anything has been missed.

many thanks for your help.

Sub testme01()
Workbooks.Open Filename:="C:\bry\QRYLOGMK2\QRYLOGMK2.xls"
Windows("ENQUIRY FORM.xls").Activate

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim res As Variant

Set wks1 = Workbooks("enquiry form.xls").Worksheets("do not touch")
Set wks2 = Workbooks("qrylogmk2.xls").Worksheets("sheet1")

res = Application.Match(wks1.Range("a14").Value, wks2.Range("a:a"), 0)

If IsError(res) Then
'Invalid Query Number!'
Else
wks1.Range("B14:BR14").Copy _
Destination:=wks2.Range("a:a")(res).Offset(0, 1)
End If


Windows("qrylogmk2.xls").Activate


ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[Dave Peterson]

It looks ok to me. But if you stop the code before the windows().activate, is
the data pasted into the worksheet?

Sub testme01()
Workbooks.Open Filename:="C:\bry\QRYLOGMK2\QRYLOGMK2.xls"
Windows("ENQUIRY FORM.xls").Activate

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim res As Variant

Set wks1 = Workbooks("enquiry form.xls").Worksheets("do not touch")
Set wks2 = Workbooks("qrylogmk2.xls").Worksheets("sheet1")

res = Application.Match(wks1.Range("a14").Value, wks2.Range("a:a"), 0)

If IsError(res) Then
msgbox "Invalid Query Number!"
Else
wks1.Range("B14:BR14").Copy _
Destination:=wks2.Range("a:a")(res).Offset(0, 1)
'only save it if you changed it
wks2.parent.save
End If

wks2.parent.close savechanges:=false

End Sub

 

[Guest]

Thanks again for your assistance Dave

All now sorted and working perfectly - many many thanks