If Cell C3 (Sheet2) is equal to any value in Column A (Sheet1) then Copy Matched row from Sheet 1

[Realjoshtodd]

I'm needing some help with creating a VBA code for the following:

If a Name is entered into Cell C3 on Sheet2 in Workbook and it matches any of the Names in Column A on Sheet1. Then I want to copy the entire row of data in sheet1 to Row1 in Sheet2.

 

[L. Howard]

I'll go with moving the columns B1, C1, D1, E1 to B/C, D/E, F/G H/I.

Howard

 

[L. Howard]

Try this in the sheet module of sheet Daily Alignment.
Then enter a date in any cell in row 1 on sheet Daily Alignment.
If you want to clear the data, just delete the date. Two columns will clear themselves. (down to about 300 rows).

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'/Claus @ MS PUBLIC

If Intersect(Target, Range("1:1")) Is Nothing Or _
   Target.Count > 1 Then Exit Sub
   
Dim c As Range, rngC As Range
Dim LRow As Long, LCol As Long, i As Long, n As Long
Dim varData1 As Variant, varData2 As Variant, varOut() As Variant
Dim NumToFind As Date

NumToFind = Target
If NumToFind = 0 Then
   Target.Offset(1).Resize(300, 2).ClearContents
   Exit Sub
End If

With Sheets("Deputy Availability")
   LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
   LCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
   Set c = .Range(.Cells(2, 3), .Cells(2, LCol)).Find(NumToFind, LookIn:=xlFormulas)
   If Not c Is Nothing Then
      varData1 = .Range(.Cells(34, c.Column), .Cells(LRow, c.Column))
      varData2 = .Range(.Cells(34, 2), .Cells(LRow, 2))
   Else
      MsgBox "Value not found": Beep: Exit Sub
   End If
   
   n = 1
   For i = LBound(varData1) To UBound(varData1)
      If varData1(i, 1) <> "" Then
         ReDim Preserve varOut(1 To 2, 1 To n)
         varOut(1, n) = varData2(i, 1)
         varOut(2, n) = varData1(i, 1)
         n = n + 1
      End If
   Next

End With

Target.Offset(1).Resize(300, 2).ClearContents
Target.Offset(1).Resize(n - 1, 2).Value = Application.Transpose(varOut)
End Sub

 

[akschary]

for this there is a formula =IFERROR(VLOOKUP(C3,Sheet2!A1:A12,1,0),"")

Try this in a standard module.

Assumes the name in sheet 2, C3 and sheet 1, column A are exactly the same.
That is, C3 = Mickey Mouse and the column A name cannot be M. Mouse or Mr. Mickey Mouse.
Each copy overwrites the previous sheet 2, row 1 entry.
If you want the cell address from sheet 1 where the name was found, uncomment the msgbox line.

Howard

Option Explicit

Sub RealjoshtoddFindIt()
Dim nmeFound As Range
Dim nmeFnd As String

nmeFnd = Sheets("Sheet2").Range("C3")

Set nmeFound = Sheets("Sheet1").UsedRange.Find(What:=nmeFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not nmeFound Is Nothing Then

  nmeFound.EntireRow.Copy Sheets("Sheet2").Range("A1")
   ' MsgBox "Match for " & nmeFnd & " in cell " & nmeFound.Address
   
Else

    MsgBox "No match found for - " & """nmeFound"""
    
End If

End Sub