Enter value in cell, have range of cells copied to another sheet

Closed
Report
-
Posts
1328
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 10, 2022
-
Hello,
I have an materials list spread sheet that when I order a line/row, I'd like that row with the amount ordered to transfer to blank sheet 2.
Example:
Sheet 1
Column A Column B Column C Column D Column E
R-1 Part # Description Price In stock Ordered

Id like it that when I place a number in Column E then A1:E1 is transferred to blank Sheet 2. Is that possible?

THANK YOU THANK YOU THANK YOU!!!!




1 reply

Posts
1328
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 10, 2022
241
Hello Ann,

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.
0