Excel macro to copy column E into today's date column and carry forward every day.

chris pike
chris pike used Ask the Experts™
on
I have a sheet that needs two buttons (macros added, one simple one to add a value into each cell beside the names, and a second to add contents of an entire column into todays column.
Have a look.
The sheet has a macro to add the values from the total points column to today and everyday after.
Whatever is in column "E" will be placed in "Todays" date column. I think if there is a button to send to "Today" that would be ok.
The set base # is easy
Everytime you need to send column E to Today there is a reason. (see Anita "Row 5"... she went from 1.5 points to 3 points, we need a note that says "on nov 30 Anita became goalie), or something I will need to put a custom note in here. )  We need to be able to capture that reason, with date, somehow.
Column E  needs to push to "Todays" column when a change is made29165634.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

Commented:
Did you attach a file?

Author

Commented:
Thanks Tom..... I updated the question with a screen capture and a xls file.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Could the base rate be 1.6 or 2.05 or is it always something like 1.0, 1.5, 2.0, 2.5 etc?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It's always .5    or    1    or 1.5    0r     2 (2 is the max for the base rate)     and if they are goalie    3
I can manually put in the additional value in the middle column and it should just up date the column E automatically,
Then Column E is just waiting for us to push the whole column into the current date

Author

Commented:
Crap. its 1 not .5 for the bottom
and 3.5 is the max because of the goalie tihing

Author

Commented:
I guess you know that the list of names could be much longer right? this is just an example.
I froze the pane so we can see just the new dates.  Your formula will create all the new dates and fill them in when the sheet refreshes right?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
So just to verify; the range for the base rate is 1 to 3.5.

When the 'Update from today' button is clicked, will both columns 'H' and 'I' be updated, or just 'I'?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please see my previous post. This workbook assumes that the answer to the first question is "yes" and that both 'H' and 'I' should be updated. Note that there was a line in the code that said
   
 If IsEmpty(Cells(3, lngColumn)) Then

Open in new window

which meant that only blank cells in columns 'H' and 'I' would be updated, and there are none so I changed it to
   
 If Not IsEmpty(Cells(3, lngColumn)) Then

Open in new window

which means that all the cells in  columns 'H' and 'I' that have values be updated.
29165656.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I also just noticed that the formula in F10 should be =SUM(Sheet1!F3:F8) rather than =SUM(F6:F8). Without the "Sheet1 part the formula won't be updated to reflect the addition of a new player.

Author

Commented:
Hi MArtin.
Let me try to clarify.

Lets say the sheet hasn't been updated in 5 days. The last column that has any date or data will be 5 days ago. (they will be blank).  When the sheet is refreshed <or maybe opened> it will continue adding dates from the last day up to todays's date with dates leading up to today and pull the amount from "E" to fill those date column and today.

Now if we change column "e" with new numbers because the base changes or someone becomes goalie or is no longer goalie and we take away the bonous points, then that column will effect todays's date column only.

When update is pressed , what ever is in E will fill in all new dates and Today, When we change e values and press again only today's date will then change with the new E values . .  If you can think of a better easier way that would be great. Personally I would love to see a way to do it where we don't have to have new column for every day. But if we do I guess we could slide over the scroll bar to show the last couple days on refresh or something? Thoughts?


The base rate will only ever be 1.0 or 1.5 or  2.0
The goalie will make 1.5 extra (on top of the base) to a max of 3.5 ever
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Lets say the sheet hasn't been updated in 5 days. The last column that has any date or data will be 5 days ago. (they will be blank).  When the sheet is refreshed <or maybe opened> it will continue adding dates from the last day up to todays's date with dates leading up to today and pull the amount from "E" to fill those date column and today.
Are all the following true?
  1. Every day at 8:00 AM if the workbook is open, a new column is added for that day and the points for that day only are calculated.
  2. At any time earlier than 8:00 AM when the 'Update from today' button is clicked a new column is added for that day and the points for that day only are calculated.
  3. If the workbook is closed on 11/30 and reopened at any time on 12/3, columns 'J', 'K' and 'L' are added and the points for those columns are calculated.
  4. Any manual change in columns 'C' and 'D' or any time the set base button is clicked, only the points in the current day will be updated.

Did I miss any other requirement?
If you can think of a better easier way that would be great.
You might want to do all this in Access where you could could have a table to store the daily data,  or open such a database from the current workbook minus columns 'H' and beyond. In either case I couldn't help you because I don't do Access.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In addition to the above, let's talk about the base rate.
Does everyone have the same rate? If so then I don't think you need column 'C', and instead you could just have a 'Current Base Rate' cell that would show the current base rate, and when clicked could ask for, validate, and then update the current days points.

Author

Commented:
Hi Martin,
#1 When ever the book is opened it should generate new columns with the all the dates at the top and fill in whatever value is in E.  …….....The total points per day column F will just be  "=sum(a:zzzz)" or something, So it is just waiting for something to drop into any cell in that row  the cells to give a total.

#2 I think we can just have it "update" on open, then we don't have to worry about a time to do it automatically.

"If we do this, Can another workbook be programmed to open this workbook to force an update to provide that second sheet fresh data that is up to date?" I can make another question for this one. I am sure it's just a VBA code to open sheet pull data thing.

#3 correct, whatever date the book, or time, is opened, it will look at the last filled in column (where the dates are), start putting all the dates up to and including today, then just copy paste column E into each and every one of the cells of that column.

#4 you got it....correct. When I open the sheet I can change the base rate for everyone, and I can change the bonus for one person, and take away the bonus from another person, and all those changes will only be placed in todays date(when button is pushed....… Hmmm. Now I am thinkning… could we prompt to enter date here, when we push the update button? Then column E would be copied to THAT date and every date up to today. . Then same rule applies as before. The whole column E will copy paste from The older entered date. Does that make sense?

I don't have Access to Access :(

About the base rate.... it doesn't really matter about the base rate or the bonus, because we will just be doing a copy paste of column E in the date columns. The base Rate, I can enter manually and the bonus I can add manually. The both will just add up by E=C+D.
I think will be the easiest for you.

Author

Commented:
Maybe this will help..
To clarify, If it has been 10 days since last opened, 10 days worth of columns will populate, and whatever was last in column E will copy paste into all of those new columns up to and including today.

When update is pressed the new data will push only into today. As shown in video
date-points.mp4
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Nice video even though November only has 30 days:)

I'll be back with an update some time today.

Author

Commented:
LOL///// Now if I could figure a skill to sell on fiverr I would be set....

OK sounds good. I am here at work for another 30 minutes
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I won't be done by then but one question before you go one question; currently points are shown with 2 decimal places, but since base rate is always one decimal place, wouldn't it be better if  the points only showed 1 decimal place?

Author

Commented:
Sure, anyway you like..... One works fine.

Author

Commented:
Do you get that a prompt to enter what date you want to copy paste E into a date column (and all dates up to and including today) would be helpful?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't think it's necessary.

Author

Commented:
Well if I don’t open the sheet for two weeks but there was a change in the base rate one week ago. I would manually have to go back to the columns and fill them in one day at a time.. I probably won’t be on top of every change on the day that the change actually happens.  Today is Friday and I open the sheet.... it populates the dates and the data from Column E... that’s great no problem.... but if last week, if there was a change I need to be able to Change my E column manually and then push the data into the chosen start date and all dates leading up to today will then be the same now.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How about a message upon open that says something like

Current base rate is 1.5 and new dates are about to be added. If the base rate has changed since 11/302019 [or whatever the last existing date is] please enter the new base rate to be applied to the new dates.

The rate would be validated to be 1, 1.5 or 2. If nothing is entered or 1.5 is entered, no change to the base rate would be made.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this. Note that the existing formula in F3:F8' is incorrect.
29165656a.xlsm

Author

Commented:
I like where you are going with this. Ok I was able to figure out how to test the sheet.
I just manually deleted the columns, and started the macro again.


I like the Set base button. But if you accidentally don't select a value, it deletes the values in C and you can not get them to come back again.

I think the logic is sound on the data push.
However the math is off/
The base rate "C" plus Bonus"D" equals Total Daily Points "E"
    If this is true then ONLY "E" should be copied to the new dates, there should not be any addition to previous dates or base rate anything.

I really think that when the sheet opens or when we refresh, it should only.. Copy paste whatever is in Column "E" to the new dates.
After open, if we need to make further updates that need to be made to previous dates the update button will just lookup the date that is inputted and copy column "E" into the chosen date and up to and including today. Let's say on the 28th the base rate changed. How can we change the date values from the 28th

In this example Nov 27 was the last time the sheet was opened so the program will update from the 27th
On the 27th the last values in column "H" were all 1.5 for everyone (no one had a bonus)
What I need is.... Column E to just copy paste into Col  "I" "J" and "K" (the new dates)
example 1
In this example Everyone was making one point on the 27th.
Wether the base rate goes up or down ONLY column "E" should copy paste into the new dates.

There a Base rate increase to 1.5, so the daily total "E" changes to 1.5 for everyone then when the new dates are created it does a copy paste of "E" into the new dates. (28th....29th....30th)
example 2
For all regular players (non goalies) the maximum you can earn is 2 points Ever per day.
The Maximum a goalie  can ever make is 3.5 points. (if base is 2.0 and 1.5 bonus equal 3.5)

Thoughts?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In this version I
  • Corrected the logic.
  • Changed the headings to look like the ones in your examples (redeemed was misspelled). If I should not have done that it is easily correctable.
  • Added a UDF called TotalPoints in column F to sum points.

You probably need a validation that does not allow the value in column G to be more than the value in column F
29165656b.xlsm

Author

Commented:
Wow MArtin,
We are 99% there,

I love how when I add more names to the list it just works.
I love how when we update from today, it just sends the correct values into empty dates up to today, or just today..

I am trying to make it bug out.
I only found one issue. I tried to delete the date in Column "H" to test the program and it started creating dates all the way across from 1900, it also left a blanks in all of column H.....This created a #VALUE error in all of column F
What happens when we have 100 columns will it delete all the data in range?

I can't change my system date to get the program to create more columns to test, but will the program scroll over automatically to show just a few of the latest date columns?. That would be ideal.

This is the error I am getting sometimes when I try to delete a range of dates.
error when trying to remove dates sometimes.

I am getting ready to post another question for this one to somehow add a comment whenever the base rate changes , up or down.
Or the bonus value changes up or down.

Great work. Thanks MArtin.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I only found one issue. I tried to delete the date in Column "H" to test the program...
In real life would you ever want to do that? BTW I just deleted the date in H2 and nothing bad happened.
2019-12-02_08-55-16.png
...will the program scroll over automatically to show just a few of the latest date columns?
I don't know what you mean when you say "scroll over".

Author

Commented:
I was deleting dates to test the vba, I agree no one should delete that column. But if someone does, it creates dates and columns from 1900

I don't think we need validation, right now I see that we are just seeing a negative and that's ok.

I think "D" is ok as it is....Not to worry there.

I was born 1967 the year "The Cage" (Star Trek Pilot) came out. My name is Christopher Pike.....Nope parents were not fans,  Not sure How I got my first name....LOL


From above...if we have 600 columns with dates, if I want to check today's date I may have to use the scroll bar at the bottom of the screen to see today's date. We have frozen column G, that's great and the new dates will continue to populate to the right forever. When we refresh the sheet I think it should automatically scroll over to the  right.....LOL I just figured out how to test it...I changed the date in the very first column "H" to Oct  27 then when I hit Update it created a months worth of date columns, it did not scroll over  to the right... See Second video "Error Scroll"
error_2.mp4
error-scroll.mp4

Author

Commented:
Ho do I make my little video editing skill into a Fiverr Skill I can sell? I am not too bad with the software that does that screen capture/video editing.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
In this version I...
  • Replaced the UDF formulas in F with built-in Excel functions. The UDF worked but it would have gotten slower as columns were added
  • Added comma when appropriate in F
  • Added scroll-left and scroll-right buttons and called the code for the scroll-right button when columns are added
29165656c.xlsm

Author

Commented:
I’m away from desk again.... my subscription is almost due again. It’s $240 .... ouch.... don’t know if if will re-new again. Will have to wait and see.

Author

Commented:
I will have to take a look at your last adjustment tomorrow

Author

Commented:
Great Job, Done exactly as to how I requested.
If I may. I would love and a adjustment.

I am opening a further question to make another modification.
Thanks Martin.
You are awesome.

Chris
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial