May 07 2022 04:46 AM
I have a spread sheet that I have a Master page on and would like certain lines to transfer to other sheets. I use it to keep track of our invoices and I have 3 collectors who will work from this same sheet. As I update the main sheet is there a way to put their initials or something in a cell and have it trigger to copy that line to their sheet? (the info in the example is fake for privacy) It would save me hours of my life if there is a way to do this. I am not super experienced with excel, so please walk me through it.
Thank you in advance for any help you can provide.
May 07 2022 05:37 AM
Sub collectors()
Dim i As Integer
For i = 3 To 16
Select Case Cells(i, 16).Value
Case Is = "RD"
Range(Cells(i, 1), Cells(i, 16)).Copy Worksheets("RD").Cells(i, 1)
Case Is = "RM"
Range(Cells(i, 1), Cells(i, 16)).Copy Worksheets("RM").Cells(i, 1)
Case Is = "MG"
Range(Cells(i, 1), Cells(i, 16)).Copy Worksheets("MG").Cells(i, 1)
End Select
Next i
End Sub
Maybe with this code. In the attached file you can click the button in cell Q4 to start the macro. The lines are copied to the collector's sheet according to the initials in column P.
An alternative could be the FILTER function if you work with Office365 or 2021.
May 07 2022 08:30 AM
May 07 2022 12:46 PM - edited May 07 2022 12:47 PM
Another option may be to use a query. Though, I think you may need a separate query for each individual. Also, the process will convert your table to an actual structured table.
Select your table, then click on the data tab and "from table" (this is where it is for me, but I don't know if it may be different for you depending on your excel version).
This should open the power query editor. It will automatically format the dates as date/time. You can keep them as just dates by selecting the "Changed Type" step, then the expand arrow on the formula bar and change the "datetime" types to just "date". Also, you could change the name of the query accordingly (let's say "RD").
Then, click the filter arrow for the "Assigned To" column and select the initials (let's say "RD"). Then, click on the Home tab, click and load to (click the bottom part of the button to show the menu and you'll see 'click and load to').
From the dialog, select "Existing Worksheet" and click on the refedit button, then use the mouse to select your RD worksheet and the cell where you want the table to be created. And load.
Then, after you add items to your master table, you can click on the data tab, and Refresh All to refresh the queries.