multi tab function

%3CLINGO-SUB%20id%3D%22lingo-sub-3337237%22%20slang%3D%22en-US%22%3Emulti%20tab%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337237%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spread%20sheet%20that%20I%20have%20a%20Master%20page%20on%20and%20would%20like%20certain%20lines%20to%20transfer%20to%20other%20sheets.%26nbsp%3B%20I%20use%20it%20to%20keep%20track%20of%20our%20invoices%20and%20I%20have%203%20collectors%20who%20will%20work%20from%20this%20same%20sheet.%26nbsp%3B%20As%20I%20update%20the%20main%20sheet%20is%20there%20a%20way%20to%20put%20their%20initials%20or%20something%20in%20a%20cell%20and%20have%20it%20trigger%20to%20copy%20that%20line%20to%20their%20sheet%3F%26nbsp%3B%20(the%20info%20in%20the%20example%20is%20fake%20for%20privacy)%26nbsp%3B%20It%20would%20save%20me%20hours%20of%20my%20life%20if%20there%20is%20a%20way%20to%20do%20this.%26nbsp%3B%20%26nbsp%3BI%20am%20not%20super%20experienced%20with%20excel%2C%20so%20please%20walk%20me%20through%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20any%20help%20you%20can%20provide.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Kit24k_0-1651923804172.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369942i9BEC8425235216EB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Kit24k_0-1651923804172.png%22%20alt%3D%22Kit24k_0-1651923804172.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3337237%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3337324%22%20slang%3D%22en-US%22%3ERe%3A%20multi%20tab%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383668%22%20target%3D%22_blank%22%3E%40Kit24k%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20collectors()%0A%0ADim%20i%20As%20Integer%0A%0AFor%20i%20%3D%203%20To%2016%0A%0ASelect%20Case%20Cells(i%2C%2016).Value%0A%0ACase%20Is%20%3D%20%22RD%22%0ARange(Cells(i%2C%201)%2C%20Cells(i%2C%2016)).Copy%20Worksheets(%22RD%22).Cells(i%2C%201)%0A%0ACase%20Is%20%3D%20%22RM%22%0ARange(Cells(i%2C%201)%2C%20Cells(i%2C%2016)).Copy%20Worksheets(%22RM%22).Cells(i%2C%201)%0A%0ACase%20Is%20%3D%20%22MG%22%0ARange(Cells(i%2C%201)%2C%20Cells(i%2C%2016)).Copy%20Worksheets(%22MG%22).Cells(i%2C%201)%0A%0AEnd%20Select%0A%0ANext%20i%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20code.%20In%20the%20attached%20file%20you%20can%20click%20the%20button%20in%20cell%20Q4%20to%20start%20the%20macro.%20The%20lines%20are%20copied%20to%20the%20collector's%20sheet%20according%20to%20the%20initials%20in%20column%20P.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20alternative%20could%20be%20the%20FILTER%20function%20if%20you%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3337572%22%20slang%3D%22en-US%22%3ERe%3A%20multi%20tab%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337572%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20fast%20response%2C%3CBR%20%2F%3EI%20think%20I%20am%20going%20to%20have%20to%20find%20someone%20to%20actually%20set%20this%20up%20for%20me%2C..%20Unfortunately%20I%20am%20not%20super%20versed%20in%20excel.%3CBR%20%2F%3E%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Sadly%20the%20information%20you%20provided%20makes%20no%20sense%20to%20me%2C%20I%20will%20see%20if%20a%20coworker%20understands%20what%20you%20are%20telling%20me%20to%20do.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3337934%22%20slang%3D%22en-US%22%3ERe%3A%20multi%20tab%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383668%22%20target%3D%22_blank%22%3E%40Kit24k%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20option%20may%20be%20to%20use%20a%20query.%20Though%2C%20I%20think%20you%20may%20need%20a%20separate%20query%20for%20each%20individual.%20Also%2C%20the%20process%20will%20convert%20your%20table%20to%20an%20actual%20structured%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20your%20table%2C%20then%20click%20on%20the%20data%20tab%20and%20%22from%20table%22%20(this%20is%20where%20it%20is%20for%20me%2C%20but%20I%20don't%20know%20if%20it%20may%20be%20different%20for%20you%20depending%20on%20your%20excel%20version).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1651952155119.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369957iC04505B4FAAB3B60%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1651952155119.png%22%20alt%3D%22JMB17_0-1651952155119.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20open%20the%20power%20query%20editor.%20It%20will%20automatically%20format%20the%20dates%20as%20date%2Ftime.%20You%20can%20keep%20them%20as%20just%20dates%20by%20selecting%20the%20%22Changed%20Type%22%20step%2C%20then%20the%20expand%20arrow%20on%20the%20formula%20bar%20and%20change%20the%20%22datetime%22%20types%20to%20just%20%22date%22.%20Also%2C%20you%20could%20change%20the%20name%20of%20the%20query%20accordingly%20(let's%20say%20%22RD%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_2-1651952385249.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369961i1650AD2D935F9897%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_2-1651952385249.png%22%20alt%3D%22JMB17_2-1651952385249.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%2C%20click%20the%20filter%20arrow%20for%20the%20%22Assigned%20To%22%20column%20and%20select%20the%20initials%20(let's%20say%20%22RD%22).%20Then%2C%20click%20on%20the%20Home%20tab%2C%20click%20and%20load%20to%20(click%20the%20bottom%20part%20of%20the%20button%20to%20show%20the%20menu%20and%20you'll%20see%20'click%20and%20load%20to').%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20dialog%2C%20select%20%22Existing%20Worksheet%22%20and%20click%20on%20the%20refedit%20button%2C%20then%20use%20the%20mouse%20to%20select%20your%20RD%20worksheet%20and%20the%20cell%20where%20you%20want%20the%20table%20to%20be%20created.%20And%20load.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20after%20you%20add%20items%20to%20your%20master%20table%2C%20you%20can%20click%20on%20the%20data%20tab%2C%20and%20Refresh%20All%20to%20refresh%20the%20queries.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

 

Kit24k_0-1651923804172.png

 

3 Replies

@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.

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.

@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).

 

JMB17_0-1651952155119.png

 

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").

 

JMB17_2-1651952385249.png

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.