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

Ask a question EmrsnMktgAnyst 5Posts Monday October 16, 2017Registration date October 17, 2017 Last seen - Last answered on Oct 19, 2017 at 11:29 AM by TrowaD

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.


plus moins
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.
Was this answer helpful?  
Leave a comment
plus moins
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

End Function

Best regards,
EmrsnMktgAnyst 5Posts Monday October 16, 2017Registration date October 17, 2017 Last seen - Oct 17, 2017 at 12:29 PM

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?)
EmrsnMktgAnyst 5Posts Monday October 16, 2017Registration date October 17, 2017 Last seen - Oct 17, 2017 at 12:30 PM
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?
EmrsnMktgAnyst 5Posts Monday October 16, 2017Registration date October 17, 2017 Last seen - Oct 17, 2017 at 02:17 PM

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
Leave a comment
plus moins
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.

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,
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!