3
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
I've updated my original post with what I've been able to manage so far. I've only done edits of recorded macros in the past so this is mostly new to me. Thanks for the info. It did help me locate examples and references more relevant to my problem. – Mukkman – 2011-10-25T15:09:31.413
Glad it helped! I'm not sure why my answer was downvoted, but that's life. Do you actually need to set the current cell based on the date, or do you want to pick a cell to write to, based on the date? – Geoff – 2011-10-27T00:23:51.897
How I'd like it to function, is to have the user select a date using (validated) drop down selections, then press a macro button that will find the matching date in the summary sheet and copy all corresponding totals into the values in the row of that date. I'm just not certain how to write in VBA, "the date is in this cell, find it in my summary page and fill in the totals." – Mukkman – 2011-10-28T14:27:26.330
I updated my answer to include an example of something similar to what you're talking about. – Geoff – 2011-10-28T17:01:59.733
Almost there. Right now I can't seem to get the variables to match properly though. In the summary sheet I have a list of Dates created by a formula. The SelectedDate comes from a validated drop down selection (from a range also created by formulas). When I run the macro doesn't update unless I change the dates in the summary sheet to the same date using the validated drop down method. Any ideas? OP updated. Does this make sense? – Mukkman – 2011-11-07T19:16:09.923