find value and paste contents of cells

B

Bry

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

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
 
B

Bry

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
 
D

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
 
B

Bry

Thanks again for your assistance Dave

All now sorted and working perfectly - many many thanks
 

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