Copy Rows from one sheet to others based on a condition [Solved]
EmrsnMktgAnyst5
Posts Monday October 16, 2017Registration date
October 17, 2017
Last seen
-
Oct 16, 2017 at 06:29 PM - Latest reply: TrowaD2392
Posts Sunday September 12, 2010Registration dateModeratorStatus
July 16, 2018
Last seen
- Oct 19, 2017 at 11:29 AM
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.
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?)
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
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?)
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.
has helped 1705 users this month