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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,833
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 20, 2018
Messages
3
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,833
Office Version
  1. 2013
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,833
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,143
Messages
5,527,072
Members
409,743
Latest member
sukuto20

This Week's Hot Topics

Top