I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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

"Miss Marple" wrote:

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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

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

"Joel" wrote:

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

"Miss Marple" wrote:

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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

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" wrote:

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

"Joel" wrote:

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

"Miss Marple" wrote:

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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

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" wrote:

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" wrote:

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

"Joel" wrote:

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

"Miss Marple" wrote:

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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

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


"Miss Marple" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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

"Miss Marple" wrote:

I have been trying unsuccessfully to plagerise the macro contained within the
response -
http://www.microsoft.com/office/comm...577&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