Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I am again trying to figure out how to write VB for making a macro autofill from data on a Data Entry page onto a Data Base page.

0

I am trying to making data from my Data Entry worksheet go to my Data Base worksheet using macros. I have been working on the formatting of the data base, and the data entry pages. I only having problems when it comes to writing the code to make what I want it to do. 

Exactly what I am trying to do...

I am taking raw data from daily numbers inputting them onto my Data Entry page by employees. I want that data to auto populate onto my Data Base page. After that the formulas that I have added to my Data Base page will enturn auto fill my report pages.

I am also trying to make the month move with the day. I had it on an active x slider bar, but removed it due to a friend explaining that it could be written as a macro more efficiently. 

I will enclose the workbook so everyone can look and help me out as much as possible.

Thank you,

Jeff Smith

Answer
Discuss

Answers

0

Much to my regret I can't make out what you want. I understand that you want to move data from, for example, de_Receiving to db_Receiving but I can't see the system by which this transfer is to be done. The de sheet is a "daily" record. It seems to require that the same date is entered 24 times (once for each employee). If all employees happen to work on that day 24 rows of entry would be required in the db (one for each employee ID). For the rest of them, the columns in the db sheet don't seem to match the columns in de. Moreover the de sheet appears to be intended to eventually call up data from the db as well as entering data into it. That isn't a good plan!

I think your question is too broad for this forum, certainly too broad for me. I suggest you pose your question for just two sheets, for example de_Receiving and db_Receiving. Explain exactly what needs to be done. Show a relationship between the data in de and those in db. Right now there are lots of data in db but none in de. I have no way of knowing where the db data came from. Therefore I also can't design a way to put them there.

Just so that you don't go empty on your second attempt, here is what I have done in the attached workbook.

  1. de_Flight Clothing was moved one step to the left so that the sequence of de and db sheets matches.
  2. Columns in the db_Employees sheet were switched. This is to enable automated lookup of ID numbers. (It turned out eventually that it wasn't necessary. If you want the old sequence back my actions 4 and 6 (below) need to be tweaked.)
  3. Deleted the reference to a downloaded workbook in the named range "EMPLOYEES". (Use the Name Manager to do some maintenance on your named ranges.)
  4. Added code on the code sheet for db_Employees. This code deletes the existing named range "Employees" and replaces it with a range covering the number of employees actually listed. It then sorts the list alphabetically. The code runs when you deactivate the sheet, meaning when you switch from that sheet to another. It will not take any action if the range size has not been changed which means that the list will not be sorted if you just change a name without changing the number of employees in the list. (See the comments in the code for a way to force sorting.)
  5. In cell de_Receiving!C7 I created a data validation drop-down which loads the list from the named range "Employees". This is a sample which you can copy widely in your workbook. The idea was already there. I just implemented it. If you wish to adjust the employee list based on an external workbook I suggest you sync the data in the db_Employees sheet with the external source.
  6. I created a formula in cell de_Receiving!D7 to read the ID number from the db_Employee tab depending upon the name selected in C7. You will be able to put this formula to good use throughout your workbook, I'm sure.
Discuss
0

hi

Discuss

Discussion

If you have a question, please click the Ask Question button in the forum and ask your Question there.
don (rep: 1989) Sep 14, '19 at 10:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login