I answered my own question. Guess I should have changed the question for
here.....I needed to copy/paste data based off a matching cell. Here is the
code:
I have an Excel Workbook with two work sheets. It is a bunch of information
about some of our computers. The first sheet has about 348 rows and the
second one has about 480 rows. Each sheet has a little bit different
information. One may have the user name and the other has the phone number.
I wanted a way to make Excel (since I'm so lazy) compare the first column of
each sheet. This column has the unique computer name. I wanted it to look
at these two columns and when it finds a match copy/paste that row next to
it's match on the other sheet.
Sub FindRowAndCopy()
' Use the first column of the CurrentRegion for lookup values
' Find matches on another sheet and copy the row back
' to the matching row on the active sheet
Dim ActiveSht As Worksheet
Dim FindMatchOnSht As Worksheet
Dim myCell As Range
Dim PasteCol As Integer
Dim LastCol As Integer
Dim r As Long
Application.ScreenUpdating = False
'use Sheet2 for finding matches
Set FindMatchOnSht = ActiveWorkbook.Worksheets("Sheet2")
With ActiveSheet
'Use the first column of the range with the cellpointer for lookup values
'Change the columns(1) value to use a different column
Set rng = Intersect(ActiveCell.CurrentRegion, .Columns(1))
'Find the last column with data and add 1 (one way to do it)
'assumes column 256 is blank
PasteCol = Cells(ActiveCell.Row, 256).End(xlToLeft).Column + 1
End With
For Each cell In rng
'search the first column on the sheet for the first match
'change the Columns(1) to search a different column
Set myCell = FindMatchOnSht.Columns(1).Find(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not myCell Is Nothing Then
'If a match is found
r = myCell.Row
LastCol = FindMatchOnSht.Cells.SpecialCells(xlLastCell).Column
'Copy and paste the row
FindMatchOnSht.Range(FindMatchOnSht.Cells(r, 1), _
FindMatchOnSht.Cells(r, LastCol)).Copy _
ActiveSheet.Cells(cell.Row, PasteCol)
End If
Next cell
Application.ScreenUpdating = True
End Sub