Transfer data from one sheet to another [Closed]

- - Latest reply: themorgan
Posts
71
Registration date
Tuesday August 8, 2017
Status
Member
Last seen
September 8, 2017
- Aug 15, 2017 at 04:20 AM
Hello, i have started an inventory sheet on excel what I want to do is take the list of inventory and when it is sold, have it transfer to the second sheet, how would I go about doing this automatically without copying and pasting each time? Thanks for any help!
See more 

1 reply

Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
206
0
Thank you
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:-


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.
themorgan
Posts
71
Registration date
Tuesday August 8, 2017
Status
Member
Last seen
September 8, 2017
11 -
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.