macro for copying between Workbooks

  • Thread starter Thread starter Miss Marple
  • Start date Start date
M

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
 
You just need to use copy with a destination

Thisworkbook.sheets("Sheet1").Range("A1:D5").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("A1:D5")
with workbook("abc.xls").sheets("sheet2")
source.copy destination:=.Range("C2")
end with
end with
 
Thank you for your response - I am unsure, however, where to insert this in
the existing code.
 
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.
 
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"
 
Do this code help?

Sub test()

With ThisWorkbook.Sheets("Sheet1")
Set Source = .Range("A1:D5")
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: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
 
Back
Top