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

Copy data from one sheet to another based on today's date

0

I am looking for some help.

The third sheet (Data entry) is a sheet that the data will change every day and needs to be copied to one or both other two sheets (location1 or location 2)Based on today's date. There will be days were no data is input for one or both location's.  I am thinking it should be a macro that is run when data is finished being collected.

I am including a test sheet. Thank you in advance!!

Answer
Discuss

Discussion

Your idea will give you trouble every Monday because the data you will be processing will not be "Today's". More trouble if the user falls sick (corona virus anyone?) and more than one day's worth of data have to be processed? So, you should make a rule to process either all the data in an entry sheet (and perhaps delete them once done or prevent entries of same date be duplicated: over-write existing to allow corrections or just refuse to process), or only the last one, or all marked ones. 
Talking of marks, columns C and G in Location2 and columns D and H in Locations1 are left blank. Ideally, columns in Entry and Location should be identical. If the ideal case isn't possible both Locations should be identical. Why must they be different in all three worksheets?
Variatus (rep: 4889) Mar 9, '20 at 9:34 pm
Colum A on the Location sheet contains a whole years date range, I would like the macro to look for Todays Date and copy into the appropriate rows. This process is done every day by someone regardless if they are sick or its a holiday. If the Data on The Entry sheet Is not changed, patialy changed or is blank the data still would get copied every day by running the macro.
The workbook the macro will be used in is over 40 sheets. We have been using pen and paper to go to each location and write down the readings every day then enter the data into excel. I would like to use a tablet enter the data, but the navigation is difficult on the tablet. So I would like to use a singel worksheet to enter the data without moving from sheet to sheet. The columns left blank are for formula's. I cant just copy the entire row, it needs to insert the data cell by cell.
This seems to work, but i dont know how to extend the range of the offset to match the range of cells.

Sub CopyData()
Dim myDate As Date
Dim myValue As Variant
myDate = Worksheets("entry sheet").range("A1").Value
myValue = Worksheets("entry sheet").range("B3:C3").Value  
'Note that this will overwrite whatever was already in col B Worksheets("location1").range("A:A").Find(myDate).Offset(0, 1).Value = myValue          
End Sub
Justin2010 (rep: 8) Mar 10, '20 at 11:18 am
Justin,  this is half a century of experience screaming at me "DON'T DO IT". So, please help me and I'll help you in return. The way you describe your system "the most recent date" in the Entry sheet is always today. Therefore the two expressions are interchangable. Believe me, "Today" is a troublemaker. Anyway, if the rows are appended daily, is it possible that the row to be inserted could be not the last row?
If the macro is run multiple times with the same qualifying date the Location sheets will be over-written. Supply the formulas. They should be entered as each row is created. In fact, if the calculations are local (within the same sheet) I would recommend values to be inserted by the code, not formulas.
Finally, if "Location1" and "Location2" are placeholder names for 40 locations you want a loop to call 40 sheets. But which of the sheets have the column arrangement of Location1 and which are of the Location2 type? Is there a third type, perhaps?  I appreciate your own effort, Justin, but now that you have help better avoid piecemeal. Just design everything to the final detail and get it done in one go.
Variatus (rep: 4889) Mar 10, '20 at 9:32 pm
The rows are already dated from the begining of 2017 to the end of 2025. My workbook contains aprox. 40 sheets but after doing an actual count there are only 17 that I want the code to run through. Location 1, 2, 3, ...17. Unfortunatly each locations worksheet has a slightly different column arrangement. There may not be an entry in every column in all 17 sheets every day, but ther will be and entry in at least one of the columns in all 17 sheets every day. Every day is a new row, Column A just contains the date, and two or three other columns contain formula's. The maco I mentioned before can be run several times a day, it wont matter as the data will not change till the next day. My formulas are local and i would like to insert value's via the code, But that will have to be after I get this working. And to answer your final coment, I havent heard of the looping till now, I have done a little reading on the looping process and i think it will work, but as i Mentioned each sheet's column arrangement is slightly different.
Justin2010 (rep: 8) Mar 12, '20 at 8:46 am
Give me a few days and I will work out something that we can then  modify into something else you can use.
Variatus (rep: 4889) Mar 12, '20 at 10:34 pm
Add to Discussion

Answers

0
Selected Answer

In the attached workbook there is code in the code sheet of the Entry Form and the standard code module UpdateReports. The latter can be dragged into your project in the VBE's Project Explorer window while both workbooks are open (this doesn't move the module but creates a copy). The former is best copied by making a copy of the sheet in your project using the Move or Copy function available from right-clicking on the tab. The code will be copied together with the sheet.

Having copied all code to your project (hopefully, a disposable copy of the same :-)) please start with reading the text I inserted in the Location 10 tab in my workbook. Then adjust the tab and code names of the worksheets in your project accordingly.

Read all the comments I inserted in my Entry Form. Note that, if you ever make a copy of this sheet in the same workbook you need to delete the code behind one of them because two sets of it will interfere with each other.

Continue reading comments I made on some of the other sheets. Then read the comments I wrote into the code. Don't worry if you don't understand everything on first try. It's quite a complicated little project, and on first reading you should aim no higher than to know where to look for answers when proper questions get formed later.

Having passed elementary education in the above way you are ready to start clicking. Make sure that you saved your work so far in a spare copy.

The most inviting button may be the "Update now" button at the top of the Entry Form. It will post all entries in the form and delete them in the form. (That's why I kept a copy of that form in the workbook itself.) When the form is empty clicking on another sheet and coming back to it will insert the current date at the top. This doesn't happen if the form has entries in it but you can change the date manually.

Less inviting but probably more exciting is a click in column A of the Entry form. A drop-down is shown when you click on the arrow, listing all eligible Location sheets. This list is created on the fly and updated whenever the sheet is activated. So, as you add more locations the dropdown will include them. The purpose of this feature is to make sure that there will be no typos in column A. You can use real location names in place of the generic names you have now. (The generic names persist in the code names.)

Posting is done by looping through all locations listed in the Entry form. It could be one, 40 or 400. For each data row the code takes the caption in the Entry form, looks for the same caption in the caption row of the target Location and posts the data there. It copies the cell format from the appropriate cell in Entry form's first row.  You set no formats in your sample. I inserted two "Text" formats for the purpose of testing. Whatever format you set in the first row of the Entry table will be carried to new target cells in the Locations sheets populated thereafter.

I thoroughly undermined your planning for the date column. Sorry about that. I know it was a lot of work to enter the dates until 2025. The code in the attached workbook looks for the last date in the column (I deleted some odd numbers below row 43000). If it matches the date at the top of the Entry form data will be copied to it. If that date in before the Entry date the macro will enter all missing dates from the last one found to the current one, and then use that row. I presumed you would want the blank rows even though the case probably will never happen. If the date at the end is in the future the macro will climb up the column until a matching date is found. (That will waste time if there are 1500 rows to check.) If that date isn't found the macro will insert a row at the required place. More relevant, if a non-date is encountered, as might happen when writing 1500 dates, the program will break with an error message.

There will be no error message if an item can't be posted. Remember, the macro looks for the column caption of the Entry sheet among the column captions of the target sheet. If no match is found the program will stop processing that particular line. Note that posting is done from right to left. Each posted data will be deleted. Unposted data will remain. A row with remaining data will not be deleted. Looking from right to left in the un-deleted Entry line you can see exactly which caption wasn't matched - the sheet name is in column A. The program will continue to run and post all those items it can. However, items to the left of the failed one will not be posted even if it might be technically possible.

The system of matching captions makes their sequence or location in the Location sheets immaterial. I suggest that the formulas you want to add be inserted using the same system: Look for the column caption and insert the formula result there. I will do that for you but need more information for which there is no room in this thread. Please, after you digest this solution, work out a new problem and put it in a new question. In this thread we should limit ourselves to getting the code to work that you now will have in your possession.

Edit March 17, 2020  =================================

Another copy of your project is now attached herewith. Please observe the date in the file names.

The table in the Entry tab was removed and replaced by a normal worksheet range while trying to maintain a table's appearance. You can now double-click on the cell below the last existing entry in column A to add a properly formatted additional row in this table. The changes did require many modifications in the code. However, other improvements were also implemented. Modified procedures are dated as of today.

Discuss

Discussion

Your code is fantastic, But after coping it to my workbook and the update code I recieve an error on line 50 of the entry code. I cannot seem to find the problem. Also with a table on the entry sheet, the workbook cannot be shared.
Justin2010 (rep: 8) Mar 16, '20 at 10:43 am
A runtime error would give you a message with and error number and description. Please tell me both. The same message box would offer you the choice to "Debug". Take it. Tell me which line of code is highlighted. Your statement that a table prevents the workbook from being shared sounds incredible. Could it be a compatibility issue? Like sharing on Google? Or with Excel 2003? I have added a revised copy of the project to my answer. This copy doesn't have a table anymore.
Variatus (rep: 4889) Mar 16, '20 at 8:19 pm
The first sheet that you uploaded I had run time errors 9 on line 23 and run time error on line 50. the second sheet works (I am able to share it through excel's legacy sharing). Now once it is shared i get an Error Selecting the location on the entry sheet "Run time error 1004: Aplication-defined or Object defined error". No choice to debug, just end or help. Any sugestions?
Justin2010 (rep: 8) Mar 18, '20 at 11:25 am
We're in the fine print here, Justin. Please strive for precision. I presume the "sheets" you refer to are workbooks. Let's forget about the first one. Just note that I don't have line numbers.
As for the second, "once it is shared" is irrelevant information. The project is installed in a particular location and then run on a certain application. At this point I wonder whether you are trying to run it remotely, meaning the workbook is stored in a location which isn't the computer at which the user sits. What's the version of Excel the program is being run on?
The program wasn't written for sharing but, with that said, the only problems I can imagine result from several users accessing the workbook at the same time. These are sharing problems. They can't be resolved by VBA. However, in order to test the program you should build confidence first. Run it in a known and normal environment for a while first before experiementing.
Error 1004 could have a myriad of reasons. Without knowing the line in which it occurs it will be tough to find the reason for. Since the program seems to have passed the first test - it runs on Excel on your machine - the fault seems to be in the sharing. So we would appear to be stuck in a loop.
Variatus (rep: 4889) Mar 18, '20 at 8:59 pm
Can you review your need for sharing this workbook? I believe you need lots of people to create entries but those same people probably don't need (preferably shouldn't have?) unimpeded access to the Locations summaries. In fact, they shouldn't even have access to the Entry form with data from others in it. Therefore the optimal solution for your needs might be a distributed entry form of which filled copies are received centrally. You might have a button on your Entry form which transfers the data from a received copy to the Entry form. There they can be reviewed and then posted with the system I have supplied - without sharing.
However, if you need to share the Location summaries no code needs to be involved because none of the posting functionality would be needed.
Variatus (rep: 4889) Mar 18, '20 at 9:33 pm
Thank you very much!! I have gotten it all to work, Your help is and was very much apprieciated.
Justin2010 (rep: 8) Mar 20, '20 at 1:06 pm
Add to Discussion


Answer the Question

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