Use tab name to look up data in data set with VBA

vba_n00b909

New Member
Joined
Mar 20, 2018
Messages
3
Hi All, longer time lurker (thanks to those who have "stealth" helped me when I've come across posts via google) first time poster. I'm trying to create a daily report to log work volumes by employee with each employee having a tab and a line of data added for each day to said tab.

I'm sorted for formatting the data, getting rid of the needless stuff etc but at the moment my way of moving the data to the right tab isn't quite right. Currently it's sorting the data alphabetically by name then using the cell location (B4 for example) to copy the data to the right tab. This works but as soon as there is a new employee or one leaves it's all going to go wrong and need re-writing for the current team members. What I want it to do instead is for example to go "this tab is called Bob Smith therefore I need to lookup the data on the input sheet for Bob Smith and copy it to Bob Smith's tab".

Could anyone help on a way to select a range of data to the left of Bob Smith's name where Bob Smith's name is taken from the tab name?

Thank you in advance
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
So are you saying you want a script to look into column "B" and see Bob Jones"
So copy that row of data to a sheet named "Bob Jones"

If that is the case:
1. Give me the name of the master sheet to copy from
2. Tell me what column we will find "Bob Jones" name. I need column number like column 2 or Column B
3. Has a sheet named "Bob Jones" already been created?
4. And after we copy the row to sheet named "Bob Jones" do we delete that row from master sheet?
5. If we do not delete the row from master how does the script know next time to ignore this row and not copy it over a second time.
6. Start on what row of sheet master
 
Upvote 0
Yea, nail on the head there :)

1. Data Input
2. Column B
3. Yes, each team member has a tab.
4. Not really needed, the data on the Data Input tab is replaced daily. The raw data is saved separately for back up
5. The script currently inputs the data on a newly created row on the relevant tab so by the end of the week there will be 5 rows of daily data. There should only ever be one row of data for each employee for any given day.
6. Row 2 below the headers for the data
 
Upvote 0
Try this:
Code:
Sub Test()
Application.ScreenUpdating = False
'Modified 3-20-18 4:25 PM EDT
On Error GoTo M
Dim i As Long
Sheets("Data Input").Activate
Dim Lastrow As Long
Lastrow = Sheets("Data Input").Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
            Rows(i).Copy Sheets(Cells(i, 2).Value).Rows(Sheets(Cells(i, 2).Value).Cells(Rows.Count, "B").End(xlUp).Row + 1)
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name does not exist or you had some other sort of problem"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You helped me a lot by answering each question I had.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
Brilliant works a treat. Thank you!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top