Forum Discussion

Kit24k's avatar
Kit24k
Copper Contributor
May 07, 2022

multi tab function

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. 

 

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 07, 2022

    Kit24k 

    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.

    • Kit24k's avatar
      Kit24k
      Copper Contributor
      May 07, 2022
      Thank you for your fast response,
      I think I am going to have to find someone to actually set this up for me,.. Unfortunately I am not super versed in excel.
      😞 Sadly the information you provided makes no sense to me, I will see if a coworker understands what you are telling me to do.
  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 07, 2022

    Kit24k 

     

    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.

Resources