A macro to help me enter data per row and then drops down to the next row to input more.

Hi. I have a problem. I've attached an example to this thread for anyone to look at. My problem is this. I work in a department that wants me to manually enter data into cells denoting the hour it was input for that department on that day it was entered. The same goes for three other departments. There are 4 departments total. The spreadsheet is listing 24 columns from 0 to 23. Each column header refers to one hour. Every empty cell below is where I enter the data matching the department that is using that hour for the day. So each department does something for that day and this report I enter lists the actions for that department for the next 30 days. Each day I input that data into the day the work was done for each department stretching to 23 hours listed on a single row. This is causing me to get headaches and cross eyes because there's no way I can be sure I'd enter the correct information until it's all entered for the day and tallying up the totals to see if they match.

I need a better and quicker way to input these values into this spreadsheet by helping me generate a macro that allows me to enter the values for each department for that day and then after entering the information, the macro copies the data to the appropriate row and then clears the input row allowing me to input new data for a different department for that day and then repeats the same action. This would allow me to simply enter the data without double checking to make sure I input it correctly on the right row where I now have to enter manually.

Originally Posted by arlu1201

In the sample file, you have Day1, Day2, etc. Is it shown the same way in your original file or do you have dates instead?

Also, how many days' data will be entered at any point of time?

It's shown exactly like this...instead of showing the dates...the "day 1, day 1, day 1, day 1" etc, is the generic way of noting it's the first day of the month, etc. It doesn't matter which month it is because the format is presented exactly like this. And it goes on like this for a full thirty days. So what I need is a macro that allows me to enter the day 1 or day 2 or day 3, etc for each department, enter the data for the hour the department was working in...and these are simple integers...like 1, or 2, or 3. Once the data is entered, it copies the data down to a row, wipes the inputted data in the first row so I can then enter new data in that row and then the macro takes that data, and drops down to the first empty row to input that data, and repeats the cycle again.

Originally Posted by arlu1201

See the attached file. I have created a separate tab for the data entry. You enter the Day no in A5 (only the number) and select the dept from the dropdown. Then enter the values for the respective hours. Click on Update. The data will automatically get populated in Sheet1.

I tried several methods to get it working, atlast landed upon a working solution. I hope its what you need.

Excellent job! I've tested this and it works. Two questions.

1. Did you name the ("d5:aa5") range when you created the macro?
2. Can I substitute ("Sheet1") with ("Sheet2") if I need to create separate sheets for the different months for analysis?

Otherwise this is a fantastic macro for data entry!! You have no idea how many hours this is going to save me plus saving my eyesight from getting cross eyed!

This macro works fine as is...but now I'm faced with an alteration request. Now I'm being asked to put the months of the year on the top but leaving the days unchanged for the first column. I thought it would simply be a case of some simple modifications. But when I did this...the macro fails to work. Can somebody please help me get the macro that was attached that caused me to initially close this case as solved to allow the user to change the top row to read just the months of the year and still work the same way? Thanks in advance.