Enter value in cell, have range of cells copied to another sheet
Closed
Ann
-
Sep 21, 2017 at 11:34 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 22, 2017 at 12:53 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 22, 2017 at 12:53 AM
Related:
- Enter value in cell, have range of cells copied to another sheet
- How to enter @ in laptop - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Excel if range of cells contains specific text then return value ✓ - Excel Forum
- Little alchemy cheat sheet - Guide
- Count if cell contains number - Excel Forum
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 22, 2017 at 12:53 AM
Sep 22, 2017 at 12:53 AM
Hello Ann,
Based on what you have described, I think that the following code may work for you:-
The code is a Worksheet_Change event and needs to be placed in the work sheet module. So, to implement the code:-
- Right click on the Sheet1 tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.
Following is the link to a little sample that I have prepared for you.
http://ge.tt/6WXcycm2
In the empty Column E, place a value in any cell then click away (or press enter or down arrow) and you'll then see that the relevant row of data is transferred to Sheet2.
If you want to clear the "used" data from Sheet1 once the transfer is completed, just remove the apostrophe(') from in front of line 11 in the code above ( the line in green font).
Test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
Based on what you have described, I think that the following code may work for you:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(5)) 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 <> "" Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
'Target.EntireRow.Delete
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code is a Worksheet_Change event and needs to be placed in the work sheet module. So, to implement the code:-
- Right click on the Sheet1 tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.
Following is the link to a little sample that I have prepared for you.
http://ge.tt/6WXcycm2
In the empty Column E, place a value in any cell then click away (or press enter or down arrow) and you'll then see that the relevant row of data is transferred to Sheet2.
If you want to clear the "used" data from Sheet1 once the transfer is completed, just remove the apostrophe(') from in front of line 11 in the code above ( the line in green font).
Test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
