Related:
- Automatically transfer data from one sheet to another in excel
- Transfer data from one excel worksheet to another automatically - Best answers
- Copy data from one sheet to another in excel - Best answers
- How To Auto Transfer Data from Master to Sub Sheets in Excel - How-To - Excel
- Transfer Data Between Excel Spreadsheets - How-To - Excel
- Automatically transfer data from one sheet to another ✓ - Forum - Excel
- Automatically update master worksheet from other worksheets ✓ - Forum - Excel
- Excel formula for copying data from one sheet to another - Forum - Excel
1 reply
vcoolio
- Posts
- 1270
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 20, 2021
Hello TFTH,
Lets assume:-
- You have headings in row 1.
- Your data starts in row 2.
- Your data stretches from Column A to Column H.
- Column H is the "criteria" column in which you type "Sold" when you are done with that row of data.
The following code may help you out:-
Once you type "Sold" in any cell in Column H and click away (or press down arrow or enter), then the entire relevant row of data is transferred to sheet2. The "used" data in the relevant row is then deleted from sheet1.
The criteria "Sold" is not case sensitive.
The code is a Worksheet_Change event and needs to be placed into the sheet module. To implement the code, right click on the sheet1 tab and from the menu that appears, select "View Code". In the big white field that then appears, paste the above code. Go back to sheet1.
Test the code in a copy of your work book first.
I hope that this helps.
cheerio,
vcoolio.
Lets assume:-
- You have headings in row 1.
- Your data starts in row 2.
- Your data stretches from Column A to Column H.
- Column H is the "criteria" column in which you type "Sold" when you are done with that row of data.
The following code may help you out:-
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(8)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
Application.ScreenUpdating = False
If Target.Value = "Sold" Then
Target.EntireRow.Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Target.EntireRow.Delete
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Once you type "Sold" in any cell in Column H and click away (or press down arrow or enter), then the entire relevant row of data is transferred to sheet2. The "used" data in the relevant row is then deleted from sheet1.
The criteria "Sold" is not case sensitive.
The code is a Worksheet_Change event and needs to be placed into the sheet module. To implement the code, right click on the sheet1 tab and from the menu that appears, select "View Code". In the big white field that then appears, paste the above code. Go back to sheet1.
Test the code in a copy of your work book first.
I hope that this helps.
cheerio,
vcoolio.
Report
themorgan
- Posts
- 67
- Registration date
- Tuesday August 8, 2017
- Status
- Member
- Last seen
- September 8, 2017
Haven't thought that there's an easy way to do so. Let me copy this on my notepad as my reference. Thanks for the tips.
Subscribe To Our Newsletter!
