macro for copying between Workbooks

[Miss Marple]

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/com...42f315-ca4c-4548-95f7-99ab681e0577&sloc=en-us

The only differences I require is the ability to copy from whatever workbook
(x) is open (could be differing names) to a specifically named worksheet (a)
in another workbook. The data also needs to be copied in columns Q-W in
worksheet (a) where the key is in D - in the varied workbooks (x) which are
received the key is in B. Thank you

 

[Joel]

You just need to use copy with a destination

Thisworkbook.sheets("Sheet1").Range("A15").copy
destination:=workbook("abc.xls").sheets("sheet2").Range("C2")

You can make this look better by doing something like this

with Thisworkbook.sheets("Sheet1")
set source = .Range("A15")
with workbook("abc.xls").sheets("sheet2")
source.copy destination:=.Range("C2")
end with
end with

 

[Miss Marple]

Thank you for your response - I am unsure, however, where to insert this in
the existing code.

 

[Joel]

I don't know if you need the other code. the website you posted has code
that performs a "find" function. If you know the range of cell s you want
copied and the location you are copying the cells to then you don't need the
other code.

 

[Miss Marple]

Hi

I do need the find facility as the "incoming" worksheet needs to update one
or two records in a master worksheet in a seperate workbook(columns Q-W).
The unique Identifier is in Column B in the "incoming" spreadsheet and Column
D in the "Master"

 

[Joel]

Do this code help?

Sub test()

With ThisWorkbook.Sheets("Sheet1")
Set Source = .Range("A15")
With Workbooks("abc.xls").Sheets("incoming")
Set c = .Columns("B:B").Find(what:="Identifier", _
LookIn:=xlValues)
If Not c Is Nothing Then
Source.Copy Destination:=.c.Offset(1, 0)
End If
End With
Set Source = .Range("E7:F10")
With Workbooks("abc.xls").Sheets("master")
Set c = .Columns("D").Find(what:="Identifier", _
LookIn:=xlValues)
If Not c Is Nothing Then
Source.Copy Destination:=.c.Offset(1, 0)
End If
End With
End With
End Sub