Lookup the value from one sheet and saved to another sheet

Abdulkhadar

Board Regular
Joined
Nov 10, 2013
Messages
119
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
Hello,

Does this code work as expected?

Code:
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,531
Office Version
365
Platform
Windows
Another option
Code:
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

Board Regular
Joined
Nov 10, 2013
Messages
119
Hello,

Does this code work as expected?

Code:
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

Board Regular
Joined
Nov 10, 2013
Messages
119
Another option
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,531
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,075
Messages
5,381,558
Members
401,744
Latest member
Schoen

Some videos you may like

This Week's Hot Topics

Top