VBA - Excel Data Treatement across multiple sheets


New Member
Feb 23, 2019
Good morning everyone! I know the text is long but please don't leave just yet. Please keep with me and be patient, I'm a newbie, on the community and on excel!

I have this project that I could really use some inputs on how to achieve it (logic, algorithm and code-wise).
I have two Excel files that work as daily logs for two facilities I run. Each contains Operator Name, Job Type and Time on Position. They are named as 'DATE'_'FACILITY'. Note that an operator may leave is job and come back later on the same day wich will result on a new entry.

I'm in charge of, every day get the data of those two logs and insert them in a protime file that consists on JOB1,JOB2,etc as sheets and for each, lines are the dates and columns are the operator name.
I want to develop a solution that makes this automatic for me.

My general idea for this would be a script that asks me for a date and than based on that, get values from the log files and insert them in the protime sheet.

Basically my approach would be:

    "Get Date from User"
    From File 'DATE'_'FACILITY'
     For each line
       Get Operator Name, Get JobType, Get time
    In File Pro_time
     Go to JobType Sheet
     Cell(Date,Operator Name).Insert_Value
Honestly everytime I start a VBA/ Excel Automation project I always run a little bit blind, because I don't have VBA knowledge and it's a little by trial and error based on my needs.

So what do you guys think? Is this doable in the way I'm thinking? Also, can I write the script in so it run without have to open the files? Like a shortcut on desktop that works as a vba standalone script?
I'm thinking of later adding a progress bar and an output userform with details such as JOB1 Total : x Hours, etc but for now functionality is key.
Any comments, suggestions and solutions would be much appreciated. Thanks for your help in advance.

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Latest member