Macro to Copy Value - Then paste based on value

[d5itup]

I currently have a macro that grabs information from another spreadsheet and copies that information to a tab in the workbook I am using. This information is updated every week, and copied over the old information. After the information is copied I would like a marco to grab the sum of 2 cells from the copied information and paste it in a table I have set up with dates already in.

The information is copied to Sheet 2, cell A1 and A2, and will be every time. Cell B3 References the same date that is in the table.

Sheet 1 with the table looks like this.

[TABLE="width: 250"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]8/27/2012[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]9/3/2012[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]9/10/2012[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The dates in theory would go on for multiple years.

The macro would be run once a week (before new data is copied over), however not necessarily on the same date referenced above.


First time posting, however I have been referencing other threads for a few years.
Thanks in advance!

 

[EdNerd]

After the information is copied I would like a marco to grab the sum of 2 cells from the copied information and paste it in a table I have set up with dates already in.

Not quite sure this is clear. Which two cells need to be summed? Where will it get pasted?
And - as per your thread title - what about the pasting depends on what about the value?

Ed

 

[d5itup]

Sorry for not making the clear.

Sheet 2, Cells A1 and A2 are summed. There is a date in cell B2 that needs to be referenced.

Sheet 1, Column A has date values, column B has values that have already been put there, as well as blanks (where date in column A is greater than todays date).

Hopefully a clearer understanding of what I am looking for:
Sum the values on Sheet 2 Cells A1 and A2 (call this "Sum"). The value in Sheet 2 Cell B3 is used to find the correct row in Sheet 1 Column A, the "Sum" is then put in column B of that row.

Let me know if I am still not making this clear.

 

[EdNerd]

This is what I came up with:

Sub SumAndCopy()
  Dim wks1 As Worksheet
  Dim wks2 As Worksheet
  
  Dim rng As Range
  
  Dim B3date
  Dim x As Long
  
  Set wks1 = ActiveWorkbook.Worksheets("Sheet1")
  Set wks2 = ActiveWorkbook.Worksheets("Sheet2")
  
  x = WorksheetFunction.Sum(wks2.Range("A1"), wks2.Range("A2"))
  B3date = wks2.Range("B3").Value
  
  On Error Resume Next
  Set rng = wks1.Range("A1:A100000").Find(B3date)
  On Error GoTo 0
  
  If Not rng Is Nothing Then
    rng.Offset(0, 1) = x
  Else
    MsgBox "Date not found"
  End If
  
End Sub

Ed

 

[d5itup]

That worked perfectly. Thanks!!!

This is what I came up with:

Sub SumAndCopy()
  Dim wks1 As Worksheet
  Dim wks2 As Worksheet
  
  Dim rng As Range
  
  Dim B3date
  Dim x As Long
  
  Set wks1 = ActiveWorkbook.Worksheets("Sheet1")
  Set wks2 = ActiveWorkbook.Worksheets("Sheet2")
  
  x = WorksheetFunction.Sum(wks2.Range("A1"), wks2.Range("A2"))
  B3date = wks2.Range("B3").Value
  
  On Error Resume Next
  Set rng = wks1.Range("A1:A100000").Find(B3date)
  On Error GoTo 0
  
  If Not rng Is Nothing Then
    rng.Offset(0, 1) = x
  Else
    MsgBox "Date not found"
  End If
  
End Sub

Ed