Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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  
share|improve this question
add comment

1 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

dim myVar1 as string
myVar1 = Sheets("Sheet1").Range("A1")

That will allow to read from any particular cell on any particular sheet.

Aggregating cell values

Dim myvar2 As Integer
myvar2 = Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B4:B6"))

That will allow you to use any of the standard worksheet functions, on any range of cells.

Writing to a cell

Sheets("Sheet2").Range("A1") = myVar1

(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.:

Dim startDate As Date
Dim currDate as Date
Dim dateOffset As Integer

startDate = #10/21/2011#
currDate = Sheets("Summary Sheet").Range("F3")
dateOffset = DateDiff("d", startDate, currDate)

Then, you can write to a particular cell in the same fashion you already are:

ActiveSheet.Range("A1").Offset(0, dateOffset) = Total1
ActiveSheet.Range("A1").Offset(0, dateOffset+1) = Total2

etc

EDIT 2:

Finding a date

Based on your comment: you can find a date in another sheet as follows:

Dim selectedDate As String
Dim rangeFound As Range
selectedDate = ActiveCell.Value
Set rangeFound = Sheet2.Cells.Find(CDate(selectedDate))

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 use rangeFound.Address you will get a value similar to $D$9. If you use rangeFound.Row and rangeFound.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

If Not (rangeFound Is Nothing) Then
    ActiveCell.Offset(0, 1) = rangeFound.Offset(0, 1)
    ActiveCell.Offset(0, 2) = rangeFound.Offset(0, 2)
End If
share|improve this answer
    
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 Oct 25 '11 at 15:09
    
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 Oct 27 '11 at 0:23
    
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 Oct 28 '11 at 14:27
    
I updated my answer to include an example of something similar to what you're talking about. –  Geoff Oct 28 '11 at 17:01
    
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 Nov 7 '11 at 19:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.