I'm trying to create a macro that will copy data from a data entry sheet into a summary sheet. The entry sheet is going to be cleared daily so I can't use a formula just to reference it. I want the user to be able to enter a date, run a macro, and have the macro copy the data from the entry sheet into the cells for the corresponding date on the summary sheet. I've looked around and found bits and pieces of how to do this but I can't put it all together.
Update:
Thanks to the information below I was able to find some additional data. I have a pretty crude macro that works if the user manually selects the correct cell. Now I just need to figure out how to automatically select the current cell relative to the current date.
Sub Update_Deposits()
'
' Update_Deposits Macro
'
Dim selectedDate As String
Dim rangeFound As Range
selectedDate = Sheets("Summary Sheet").Range("F3")
Set rangeFound = Sheets("Deposits").Cells.Find(CDate(selectedDate))
Dim Total1 As Double
Dim Total2 As Double
Dim Total3 As Double
Dim Total4 As Double
Dim Total5 As Double
Total1 = Sheets("Summary Sheet").Range("E6")
Total2 = Sheets("Summary Sheet").Range("E7")
Total3 = Sheets("Summary Sheet").Range("E8")
Total4 = Sheets("Summary Sheet").Range("E9")
Total5 = Sheets("Summary Sheet").Range("E10")
If Not (rangeFound Is Nothing) Then
rangeFound.Offset(0, 2) = Total1
rangeFound.Offset(0, 3) = Total2
rangeFound.Offset(0, 4) = Total3
rangeFound.Offset(0, 6) = Total4
rangeFound.Offset(0, 7) = Total5
End If
'
End Sub
This version will find the first value on the page and fill in values:
Sub Update_Deposits()
'
' Update_Deposits Macro
'
Dim selectedDate As String
Dim rangeFound As Range
selectedDate = Sheets("Summary Sheet").Range("F3")
Set rangeFound = Sheets("Deposits").Cells.Find(CDate(selectedDate))
Dim Total1 As Double
Dim Total2 As Double
Dim Total3 As Double
Dim Total4 As Double
Dim Total5 As Double
Total1 = Sheets("Summary Sheet").Range("E6")
Total2 = Sheets("Summary Sheet").Range("E7")
Total3 = Sheets("Summary Sheet").Range("E8")
Total4 = Sheets("Summary Sheet").Range("E9")
Total5 = Sheets("Summary Sheet").Range("E10")
If Not (rangeFound Is Nothing) Then
rangeFound.Offset(0, 2) = Total1
rangeFound.Offset(0, 3) = Total2
rangeFound.Offset(0, 4) = Total3
rangeFound.Offset(0, 6) = Total4
rangeFound.Offset(0, 7) = Total5
End If
'
End Sub
Best Answer
Since you're starting with a macro solution, I'm assuming you have enough programming knowledge to put the final result together; so given that, the main pieces I think you need are:
Get started
Make sure the developer ribbon is showing, then switch to the VBA editor (Alt-F11).
Read through this page to get an appreciation of the overall process; some of the examples may be very close to what you want (e.g. 'Automatically Creating Workbook Files from Worksheet Data').
Reading a cell
That will allow to read from any particular cell on any particular sheet.
Aggregating cell values
That will allow you to use any of the standard worksheet functions, on any range of cells.
Writing to a cell
(simply the reverse of reading)
Connecting the macro to a button on a sheet
After you create your macro, add a button to your master sheet wherever makes the most sense, following this guide.
Good luck!
EDIT 1:
Writing based on offset
To write to a particular cell based on a date offset, you could use the DateDiff function to get the distance to offset, e.g.:
Then, you can write to a particular cell in the same fashion you already are:
etc
EDIT 2:
Finding a date
Based on your comment: you can find a date in another sheet as follows:
Obviously you'd change
Sheet2
to whatever you wanted. That will give you the address of the cell containing the date on the summary sheet. If you userangeFound.Address
you will get a value similar to$D$9
. If you userangeFound.Row
andrangeFound.Column
you will get the actual row and column numbers. Note that you also have the address of the active cell through the same principle (e.g.ActiveCell.Row
). From there, you can copy values using something like