Lookup the value from one sheet and saved to another sheet

[Abdulkhadar]

Hello Excel Experts,
I want to save the data from Data Entry Sheet to Storing Sheet. At data Entry sheet E1 cells have a unique number same as allready saved in Storing Sheet at Column E.
I want to save the data from Data Entry Sheet to Storing Sheet by using push button.
Lookup the number from Data Entry Sheet E1 in Storing Sheet of column E and copy the data from Data Entry Sheet of column E10 to E31 to Storing Sheet row K to AF and Data Entry Sheet E34 to E51 to row Storing Sheet AF to AX.

Thanks in advance.

 

[onlyadrafter]

Hello,

Does this code work as expected?

Sub COPY_TO_SHEET()
    Application.ScreenUpdating = False
    MY_VALUE = Sheets("Data Entry Sheet").Range("E1").Value
    With Sheets("Storing Sheet")
        For MY_ROWS = 1 To .Range("E" & .Rows.Count).End(xlUp).Row
            If .Range("E" & MY_ROWS).Value = MY_VALUE Then
                Sheets("Data Entry Sheet").Range("E10:E31").Copy
                .Range("K" & MY_ROWS).PasteSpecial (xlPasteAll), Transpose:=True
                Sheets("Data Entry Sheet").Range("E34:E51").Copy
                .Range("AG" & MY_ROWS).PasteSpecial (xlPasteAll), Transpose:=True
            End If
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub

On your description did you mean to copy E34:E51 to AG:AX and not AF:AX?

 

[Fluff]

Another option

Sub CopyTrans()
   Dim Fnd As Range
   Dim Dws As Worksheet
   
   Set Dws = Sheets("Data Entry")
   With Sheets("Storing")
      Set Fnd = .Range("E:E").find(Dws.Range("E1").Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         .Range("K" & Fnd.Row).Resize(, 22).Value = Application.Transpose(Dws.Range("E10:E31").Value)
         .Range("AG" & Fnd.Row).Resize(, 18).Value = Application.Transpose(Dws.Range("E34:E51").Value)
      End If
   End With
End Sub

 

[Abdulkhadar]

Hello,

Does this code work as expected?

Sub COPY_TO_SHEET()
    Application.ScreenUpdating = False
    MY_VALUE = Sheets("Data Entry Sheet").Range("E1").Value
    With Sheets("Storing Sheet")
        For MY_ROWS = 1 To .Range("E" & .Rows.Count).End(xlUp).Row
            If .Range("E" & MY_ROWS).Value = MY_VALUE Then
                Sheets("Data Entry Sheet").Range("E10:E31").Copy
                .Range("K" & MY_ROWS).PasteSpecial (xlPasteAll), Transpose:=True
                Sheets("Data Entry Sheet").Range("E34:E51").Copy
                .Range("AG" & MY_ROWS).PasteSpecial (xlPasteAll), Transpose:=True
            End If
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub

On your description did you mean to copy E34:E51 to AG:AX and not AF:AX?

Wow Nice it works perfect Thanks once again.

 

[Abdulkhadar]

Another option

Sub CopyTrans()
   Dim Fnd As Range
   Dim Dws As Worksheet
   
   Set Dws = Sheets("Data Entry")
   With Sheets("Storing")
      Set Fnd = .Range("E:E").find(Dws.Range("E1").Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         .Range("K" & Fnd.Row).Resize(, 22).Value = Application.Transpose(Dws.Range("E10:E31").Value)
         .Range("AG" & Fnd.Row).Resize(, 18).Value = Application.Transpose(Dws.Range("E34:E51").Value)
      End If
   End With
End Sub

This is also works, nice thanks once again

 

[Fluff]

Glad we could help & thanks for the feedback