Previous Topic Next Topic

Transfer data from 1 sheet to another with cond.

[Solved/Closed]
Report
Posts
2
Registration date
Saturday March 17, 2018
Status
Member
Last seen
March 21, 2018
 -
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
 -
Hi,

I am having trouble working out how I would code this using VBA:

In Sheet 1 I have 2 rows. The first row is the title and then the second row has the title for each column (e.g name, DOB, age, next of kind, in system, start, end, location, action). In the 'action' column I have a drop down box with 5 options in it (e.g. assessment, group tx, individual tx, transfer, discharge) - each option corresponds to a different Sheet. What I want to happen is when I add in data for a person into a row and then select a specific 'action' the data is automatically copied to the relevant sheet. I only want the data to be copy. I want it to still remain on the initial summary sheet. How would I code this in VBA? I can not seem to get it to work with the condition.

Thank you!

2 replies

Answer 1 / 2
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
 239
Hello Kate,

Try the following code placed in the Summary sheet module:- 
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns("I:I")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
Sheets(Target.Value).Columns.AutoFit

Application.EnableEvents = True
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code is a WorkSheet_Change event and needs to be placed in the Summary sheet module. To implement the code:-

- Right click on the Summary sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

I've assumed that the "Action" column is Column I.

Every time that you select an action from the drop downs in Column I, the relevant row of data will be transferred to the relevant sheet. You must make sure that the spelling in the drop downs (inc. cases, punctuation, spaces etc.) is exactly the same as the tab spelling.

Following is the link to a little sample that I have prepared for you:-
http://ge.tt/91PBR3p2

Select an action from the drop downs in any cell in Column I then click away (or press enter or down arrow) to see the code work.

I hope that this helps.

Cheerio,
vcoolio.
Report
Posts
2
Registration date
Saturday March 17, 2018
Status
Member
Last seen
March 21, 2018
Thank you so much! This is perfect.
Answer 2 / 2
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
 239
You're welcome Kate. I'm glad that I was able to help.

Cheerio,
vcoolio.
Ask a question