Transfer from one worksheet to another with a codition
Closed
stamcos
-
Jul 31, 2015 at 12:07 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Aug 1, 2015 at 05:11 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Aug 1, 2015 at 05:11 AM
Related:
- Transfer from one worksheet to another with a codition
- Transfer data from one excel worksheet to another automatically - Guide
- Free fire transfer - Guide
- How do you add a username or author to a worksheet - Guide
- Automatically run a macro when opening a worksheet - Guide
- How to transfer contacts from sim to phone - Guide
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Aug 1, 2015 at 05:11 AM
Aug 1, 2015 at 05:11 AM
Hello Kostas,
From what I understand, you have a long list of items and would like to search for a particular serial number for a stock item in a "Stock" sheet and have the full details of that item displayed in a separate sheet. If so, the following code may do the task for you:-
The code uses an Input box (sheet1) in which you enter the serial number to be searched and when found, will transfer the whole row of data associated with that serial number to sheet 2.
I have attached my test work book for you to peruse here:-
https://www.dropbox.com/s/xdakxow7l4r6fct/Kostas.xlsm?dl=0
Click on the "Search Serial Nos." button to execute the code and bring up the input box. Enter a serial number from the fictitious list to have that row of data transferred to sheet 2.
I hope that this helps.
Cheerio,
vcoolio.
From what I understand, you have a long list of items and would like to search for a particular serial number for a stock item in a "Stock" sheet and have the full details of that item displayed in a separate sheet. If so, the following code may do the task for you:-
Sub FindStuff()
Application.ScreenUpdating = False
Dim sNumSearch As String
sNumSearch = InputBox("Please enter the serial number to search for.")
If sNumSearch = vbNullString Then Exit Sub
With Sheets("Sheet1")
.AutoFilterMode = False
With Range("E2", Range("E" & Rows.Count).End(xlUp))
.AutoFilter 1, sNumSearch
On Error Resume Next
.Offset(1).EntireRow.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub
The code uses an Input box (sheet1) in which you enter the serial number to be searched and when found, will transfer the whole row of data associated with that serial number to sheet 2.
I have attached my test work book for you to peruse here:-
https://www.dropbox.com/s/xdakxow7l4r6fct/Kostas.xlsm?dl=0
Click on the "Search Serial Nos." button to execute the code and bring up the input box. Enter a serial number from the fictitious list to have that row of data transferred to sheet 2.
I hope that this helps.
Cheerio,
vcoolio.
