Forum Discussion
Which code to use?
If I'm understanding your situation, you download hours for 30 employees, but it comes as an excel workbook, where each employee has a sheet of their own, and you are trying to combine all of that into one sheet without all the manual navigating and copy/pasting. Is that correct?
liverlarson Yes i use hours to that data. but not exactly combine them. what i do is one sheet is where i have all the data, all the other 30 sheets are where data i have to upload for each employee its one sheet. what i need help with is to write a code in each of those 30 sheets instead of me tiping the name/employee number in that row the data will do that for me and load their working hours. but so for each sheet the data will know where the names on previous sheets have been used and use the remaining one on the list.
Example is: 3 employees, their name are David, Troy, Elsa. in sheet 1 the data will get any of names first on the list and use it. sheet nr 2 will minus the used name which is lets say David and extract another name and use it. the third sheet will then minus 2 name used in previous sheets and use the last remaining on the list.sorry for long text and thanks for the help
ok, so other way around. You've got one dataset, and you want to copy each person within that dataset to their own sheet. From the sounds of it, the names on the single dataset are not known, so you're looking for a way to automatically scrape the names from the main dataset, and use each one to create a distinct page just for that name.
If this is correct, than I would say we're talking about automation more than formulas, and that means VBA. This is something I don't typically get into on forums, as solutions tend to be custom and complex, and requires a fair amount of background knowledge for any of it to be helpful. So, I would bow out of this conversation and let someone chime in who likes writing/consulting on VBA and macros.
The other option that comes to mind is leveraging PowerQuery and the M language, but again, it's going to be pretty technical to get to a workable solution.
The only other option that comes to mind is that instead of copying EACH person to a sheet of their own, create a REPORT page, where you select a person, and the page poulates with that person's data, so you can just select from a drop down of person names, and run them one at a time. That could be done with a pivot table, or PowerQuery, or just a bunch of formulas that look to the selected person control cell you set up on the report page.
Forgive me for being obtuse, but there are a lot of options and details, and without a concrete example, all I can do is paint in broad strokes.
It is certainly possible, but for a "noob" I would say it is a lot to bite off.
liverlarson i see thanks for the help. im trying to code my file to ease my work and that was the last thing i wanted on my file to completely reduce the time spent on it.
