How do I create a VBA macro that will copy data from an entry sheet, into a summary sheet by date
- by Mukkman
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
