You're gonna have to explain in plain words what you want it to do--including
details.

GTyson2 wrote:

You have the Idea right. I just try to input it, but it's not copying the
cells. It does open the other workbook and copy the Sheet for me, then Closes
that workbook wich is great. But as I said it's no longer pasting the cells.

"Dave Peterson" wrote:

I'm confused by what you're doing, but maybe I have it right????

It looks like you're opening a workbook, copying a sheet to an already open
workbook.

Then cycling through the cells in column B (B4 to the last used cell in column
B) in that newly pasted sheet.

You're looking for a match for the value in B4 (then B5, B6, ...) in column C of
the same sheet. If there is a match, then copy the value in column 14 on that
matching row into column 20 of the cell that we're looping through.

Am I close?????

If yes, then this compiled, but it's untested.

Option Explicit
Sub myMatch()

Dim res As Variant
Dim fName As Variant 'could be boolean
Dim TempWkbk As Workbook
Dim TrackWkbk As Workbook
Dim AcctWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range

'Thisworkbook or Activeworkbook???
Set TrackWkbk = Workbooks("Tracker-test.xls")

fName = Application.GetOpenFilename()
If fName = False Then
'user hit cancel
Exit Sub
End If

Set TempWkbk = Workbooks.Open(Filename:=fName, ReadOnly:=True)

TempWkbk.Worksheets("Accounting_Teams").Copy _
Befo=TrackWkbk.Sheets(1)

Set AcctWks = TrackWkbk.Sheets(1) 'the newly pasted sheet

TempWkbk.Close savechanges:=False

With AcctWks
Set myRng = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
res = Application.Match(myCell.Value, .Range("C:C"), 0)

If IsError(res) Then
'no match, ignore
Else
.Cells(res, 20).Copy _
Destination:=.Cells(myCell.Row, 14)
End If
End If
Next myCell
End With

End Sub

GTyson2 wrote:

Sorry that one was old, Replace the Z code with this
z = Application.WorksheetFunction.Match(Cells(X, Y),
Sheets("Accounting_Teams").Columns("C:C"), 0)
That's what I have now and it's not working

"GTyson2" wrote:

The Following is a code I wrote to try and Lookup information from one
workbook and copy and paste that exact information into another workbook. But
I'm having issues with my Formula for Z. I'm trying to use the Match funtion
in excel since it gives you the row number, and my column is going to be
static. Can someone help me out here?

Sub Match()
x = 4
y = 2
b = 14


Dim fName As String
fName = Application.GetOpenFilename()
Workbooks.Open Filename:=fName
Sheets("Accounting_Teams").Select
Sheets("Accounting_Teams").Copy Befo=Workbooks( _
"Tracker-Test.xls").Sheets(1)


Do While Cells(x, y).Value < ""
z = Formula = "=Match(Cells(X,
Y),Sheets(""Accounting_Teams"").Columns(""C:C""),0 )"
Sheets("Accounting_Teams").Select
Cells(z, 20).Select
Selection.Copy
Sheets("Sheet1").Select
Cells(x, b).Select
ActiveSheet.Paste

x = x + 1

Loop

Applications.CutCopyMode = False
Sheets("Accounting_Teams").Select
ActiveWindow.SelectedSheets.Delete

End Sub


--

Dave Peterson
.


--

Dave Peterson