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

[vba_n00b909]

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

 

[My Aswer Is This]

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

 

[vba_n00b909]

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

 

[My Aswer Is This]

Try this:

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

 

[vba_n00b909]

Brilliant works a treat. Thank you!

 

[My Aswer Is This]

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!