Copy formula down one row and paste values in the row the copy originated from.

I have a spreadsheet that gets opened automatically every day to pull data from other sources. I have a list of dates for every work day for the year in column A. In column B in the first row I have a formula to start. None of the other rows in column B have this formula yet. What I need to do is when the file is opened every morning automatically I need the formula to be copied down to the row that has the current date in column A and then paste the values in the row where the copy originated from. For example if A1 is 1/17/18 and the formula is in B1 I need to take that formula and copy it to B2 since A2 is 1/18/18. And then paste the value that is already in B1 so the formula is no more in B1 and is now in B2. How can this be done in VBA?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Lawrence,

Paste below code in your workbook open event where you already have macro to automatically update your workbook:
Private Sub Workbook_Open()
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
Application.ScreenUpdating = False
Ws.Range("B1").Copy
Ws.Range("B2").PasteSpecial xlPasteFormulas
Ws.Range("B1").Value = Ws.Range("B2").Value
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Open in new window

0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you for the help! Can I just change the ranges if I wanted to continue this down till the last date in column A?
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
It needs to copy the formula down to the next cell but it also needs to make sure the formula is copied to the row that has the current date in column A. Column A has all the work days for 2018 in it. So currently the formula will be in the row where 1/18/18 is in column A. So when the file opens tomorrow it needs to copy and paste the formula in the next row where the date in column A is 1/19/18 and then paste the values in column B and  where the date is 1/18/18 in column A.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ShumsDistinguished Expert - 2017Commented:
Oh Ok,

I thought you just want to change B1 & B2,

Try below:
Private Sub Workbook_Open()
Dim Ws As Worksheet
Dim LRow As Long
Dim DateRng As Range, d As Range
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Set DateRng = Ws.Range("A1:A" & LRow)

For Each d In DateRng
    If d = Date Then
        d.Offset(1, 1).Formula = d.Offset(0, 1).Formula
        d.Offset(0, 1).Value = d.Offset(1, 1).Value
    End If
Next d
Application.ScreenUpdating = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Hi...sorry for the delay in testing this. When I run this code I get a 'Run-Time Error '9': Subscript out of range.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That was my error. I forgot to change the sheet name.
0
ShumsDistinguished Expert - 2017Commented:
Please test again with appropriate sheet name and let me know.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yup. It's working the way it should. If I wanted to change it to look for the previous workday (exclude weekends  & holidays) instead of the current day, how would the code look? Since this file will be opened early in the morning before business hours I want it to pull the data by looking for the previous work day in column A instead of the current day. I have a named range called "Holidays" in the file. I didn't of this until just now.
0
ShumsDistinguished Expert - 2017Commented:
Hi Lawrence,

As per your initial requirement, my last provided code works well. If you have any other query, I would request you to please close this question and raise a new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.