Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

Copy Rows from one sheet to others based on a condition [Solved/Closed]

Report
Posts
5
Registration date
Monday October 16, 2017
Status
Member
Last seen
October 17, 2017
 -
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 -
Hello,

I have a workbook for our teams budget that has 1 master sheet with 28 columns. The master sheet will be updated with more rows often (adding new budget items). I would like to use a macro button so that every time a certain person (one who makes a decision for one column) updates that sheet she can click the button. What I need the macro to do is copy all of the rows that have "Pre-Pay" in the "Payment Source" column and paste them into different sheets based on what vendor is listed in another column.

I have attached an image of what the main sheet looks like since I can't figure out how to attach a file.


I'm very new to marcos so I would love if you could help create the code for me. Happy to send the file if you can tell me how.

Best,
Danielle

See more
Related:

3 replies

Answer 1 / 3
Posts
5
Registration date
Monday October 16, 2017
Status
Member
Last seen
October 17, 2017
 2
Hi Trowa,

I created a new post because I got different feedback from my boss on what she actually wanted but I couldn't figure out a way to edit my original post. If something changes, how should I update that in the future?

But to answer your questions:
1. You are correct, I don't want to create any duplicates
2. I think letting the code run automatically when data in a certain column is confirmed should work. I might have to add a new column that is "confirmed" but I will need to check with a coworker to see exactly what data we need because some data in the row will be added at a later date, but I am not sure it that data will also need to transfer to other sheets. For now can you help me create it once anything is chosen for column G?
3. Yes, per my other post there are other things I need to solve
a. I need the rows to go into certain tabs based on what is added in column E (Vendor Name). (not if it is pre-pay - i need ALL data to transfer now) However, a tab that matches that name might now always exist (so is there a way to have one added if it doesn't already exist?)

thank you for your help.
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 3
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Danielle,

To provide additional info, then post another message in the same thread as you did now. For any future requests to post you workbook, use a free (and without any sign in procedure) file sharing site and post back the download link.


Back to your query:

Since you said you are very new to macro's it is probably best to create a copy of your workbook. Actions done by macro's cannot be undone by using the blue arrows.

So a non existing sheet name can be entered in column E. I'm sure the other sheets have a specific format. Create a template sheet with that format and call it "Template".

To implement the code below, right-click the sheet "FY18 budget tracking" tab and select View Code. Copy the code in the big white field.

Remember that the code will only run when you confirm an entry in column G.

Here is the code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G")) Is Nothing Then Exit Sub

If Not SheetExists(Target.Offset(0, -2).Value) Then
    Sheets("Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Target.Offset(0, -2).Value
End If

Target.EntireRow.Copy _
Sheets(Target.Offset(0, -2).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then
    SheetExists = True
    Exit Function
End If

NoSuchSheet:
End Function


Best regards,
Trowa
Report
Posts
5
Registration date
Monday October 16, 2017
Status
Member
Last seen
October 17, 2017
 2
Trowa,

so it kind of works except i keep getting messages about a name already existing. (screen shot can be found in below link but a sample of what the data in the budget tracking tab looks like) I do have data validation that is happening in columns E and F, does that make a difference (also i added another column, does that mess with the macro?)

https://docs.google.com/spreadsheets/d/1zz9eTmLvGP9gelmm8X834TBVB99M1CXofPnpYzgyQ0g/edit?usp=sharing
Report
Posts
5
Registration date
Monday October 16, 2017
Status
Member
Last seen
October 17, 2017
 2
also, right now it looks like the maro is trying to read column F instead of E, can you show me where to fix that?
Report
Posts
5
Registration date
Monday October 16, 2017
Status
Member
Last seen
October 17, 2017
 2
Trowa,

can you see my first reply with the link to a google doc? I cannot see it on my end and want to make sure
Answer 3 / 3
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Danielle,

Data validation won't affect the code.

Adding columns won't affect the code as long as they are added after column G.

The code will run when a change is made in column G (code line 2).
The sheet name is pulled from the cell, 2 columns to the left of column G. This is done by the Offset value in code line 4. Negative 2 means 2 columns to the left.

Let me provide you with the test book I used. Maybe that will help.
http://www.ge.tt/2vIEOym2

Follow the steps in column G of the Main sheet to see it's functionalities.

I can see the link to google docs, but I don't know how to produce that message.

Do consider the option to post your workbook the same way I did mine (Always be careful with sensitive data).

Best regards,
Trowa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!