Copy Rows from one sheet to others based on a conditionSolved/Closed

Question

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

System Configuration: Windows / Chrome 61.0.3163.100

EmrsnMktgAnyst · Accepted Answer

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.

TrowaD · Answer

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

TrowaD · Answer

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

Copy Rows from one sheet to others based on a condition

3 responses

Similar discussions

Subscribe To Our Newsletter!