Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

Im looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. Ive filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub

I _think_ this does what you want.

Dim WkbkARng as range
dim WkbkBRng as range
dim myCell as range
dim res as variant 'could be a number or an error

set wkbkARng = workbooks("workbookA.xls").worksheets("sheet1").ra nge("E2:e100")
set wkbkBRng = workbooks("workbookB.xls").worksheets("sheet2").ra nge("a2:a100")

for each mycell in wkbkarng.cells
res = application.match(mycell.value,wkbkbrng,0)
if iserror(res) then
'no match, do nothing
else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res)

'or just to bring back the value
'wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
end if
next mycell


Untested, uncompiled--watch for typos.

Roger wrote:

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

Im looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. Ive filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub

--

Dave Peterson

Hi Dave,

Thank you so much for answering this- it's really been giving me a sharp
pain in the backside.

You noted that I could bring back the value by swapping the line of code as
noted below. The problem is once I swap, I get an error and I'm not sure
what I'm doing wrong.

Thanks again for your review - Roger

myCell.Offset(0, 1).Copy _
Destination:=WkbkRng(res) <<<< tried to replace as shown below


myCell.Offset(0, 1).Copy
Destination:=wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
<<< received error with above

"Roger" wrote:

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

Im looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. Ive filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub

What error did you get?

And maybe you wanted this:

mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)

Did you want to bring back the column to the right of the matching cell and
place it in the cell to the right of the original cell?

Roger wrote:

Hi Dave,

Thank you so much for answering this- it's really been giving me a sharp
pain in the backside.

You noted that I could bring back the value by swapping the line of code as
noted below. The problem is once I swap, I get an error and I'm not sure
what I'm doing wrong.

Thanks again for your review - Roger

myCell.Offset(0, 1).Copy _
Destination:=WkbkRng(res) <<<< tried to replace as shown below

myCell.Offset(0, 1).Copy
Destination:=wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
<<< received error with above

"Roger" wrote:

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

Im looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. Ive filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub


--

Dave Peterson

That is exactly what I wanted and it is now working flawlessly.

Thank you for your help and you rock!

Roger



"Dave Peterson" wrote:

What error did you get?

And maybe you wanted this:

mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)

Did you want to bring back the column to the right of the matching cell and
place it in the cell to the right of the original cell?

Roger wrote:

Hi Dave,

Thank you so much for answering this- it's really been giving me a sharp
pain in the backside.

You noted that I could bring back the value by swapping the line of code as
noted below. The problem is once I swap, I get an error and I'm not sure
what I'm doing wrong.

Thanks again for your review - Roger

myCell.Offset(0, 1).Copy _
Destination:=WkbkRng(res) <<<< tried to replace as shown below

myCell.Offset(0, 1).Copy
Destination:=wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
<<< received error with above

"Roger" wrote:

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

I€„¢m looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. I€„¢ve filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub


--

Dave Peterson

Glad you got it working--and sorry about the original typo.

Roger wrote:

That is exactly what I wanted and it is now working flawlessly.

Thank you for your help and you rock!

Roger

"Dave Peterson" wrote:

What error did you get?

And maybe you wanted this:

mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)

Did you want to bring back the column to the right of the matching cell and
place it in the cell to the right of the original cell?

Roger wrote:

Hi Dave,

Thank you so much for answering this- it's really been giving me a sharp
pain in the backside.

You noted that I could bring back the value by swapping the line of code as
noted below. The problem is once I swap, I get an error and I'm not sure
what I'm doing wrong.

Thanks again for your review - Roger

myCell.Offset(0, 1).Copy _
Destination:=WkbkRng(res) <<<< tried to replace as shown below

myCell.Offset(0, 1).Copy
Destination:=wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
<<< received error with above

"Roger" wrote:

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

I€„¢m looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. I€„¢ve filled in the ranges as they would appear in each of the Workbooks.
Range(€œE2:E100€�) is the range in Workbook A looking to match Range(€œA2€�A100)
in Workbook B. If match is found, Workbook A Range (€œB2:B100€�) would copy
cell values to destination Workbook B Range(€œ€�C2:C100€�).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub


--

Dave Peterson


--

Dave Peterson