Copy value of cell if date matches the date of another sheet

[nbench]

I have two sheets in the same workbook, one is a daily sheet and the other is a monthly summary. The same file is saved over and over again with just a change of name and date.

What I am trying to do is check the date on the daily sheet, and copy and paste a value from that sheet into the second spreadsheet on the appropriate row/column


I am using excel 2010

EXAMPLE:


(sheet 1)
Date is cell (J, 1)
I want to copy the value in cell (C, 30)
(Sheet 2)
I have the dates listed from (B7:B35) for the month
I want the value of cell (c, 30) pasted to (AO7:AO35)

thanks in advance

[nbench]

Here is an example of my code, btw... I am a rookie at best and not familiar with VBA syntax

Sub CopyBasedonSheet1()


Dim i As Long

For i = 7 To 35
If Worksheets("Daily Reports").Cells("J", 1) = Worksheets("February Summary").Cells("B", i) Then
Worksheets("Daily Reports").Cells("C", "30").Value = Worksheets("February Summary").Cells("AO", i).Value


Else
End If
Next i

End Sub

[SamT]

This is only your code, with corrected syntax. any logical errors are still included. Note two ways to use "Cells()"

Sub CopyBasedonSheet1()

Dim i As Long

For i = 7 To 35
If Worksheets("Daily Reports").Range("J1") = Worksheets("February Summary").Cells(i, 2) Then
Worksheets("Daily Reports").Range("C30).Value = Worksheets("February Summary").Cells(i, "AO").Value
End If
Next i
End Sub

I prefer to put the Range to check on the left of the equation and the Value on the right side. I still have made no changes in logic

Sub CopyBasedonSheet1()
Dim i As Long

For i = 7 To 35
'Note one line IF. Space Underscore is Line Continuation.
If Worksheets("February Summary").Cells(i, 2) = Worksheets("Daily Reports").Range("J1") Then _
Worksheets("February Summary").Cells(i, "AO") = Worksheets("Daily Reports").Range("C30").
Next i
End Sub

About the logic:
You have several Constants: Daily Reports J1 and C30, Summary Range B7:B30 and AO7:AO30
The Name of the Summary sheet Varies by month. The Summary Row with the date varies.

The Check for Sheet Exists is not included in this code

Sub VBAX_SamT_Daily2Summary()

Dim DailyDate As Date
Dim DailyValue as Variant 'Edit to fit Type of Value (Date, number Type. String, et)c
Dim SummaryDates As Range
Dim Rw As Long

With Sheets("Daily Reports")
DailyDate = .Range("J1")
DailyValue = .Range("C30")
End With

'Will fail the the correct Summary sheet does not exist.
With Sheets(Format(DailyDate, "mmmm") & " Summary")
Set SummaryDates = .Range("B7:B30")
Rw = SummaryDates.Find(DailyDate).Row

IF Rw < 7 or Rw > 30 Then
MsgBox "The Date was not Found"
Else
.Cells(Rw, "AO") = DailyValue
End If
End With
End Sub

This is off the top of my head and is not checked for errors or typos. :D

[nbench]

Rw = SummaryDates.Find(DailyDate).Row

Object variable or with block variable not set

The error above happens when I tried to run the code... Is it something obvious? Thanks

[SamT]

A Procedure is a Sub or a Function

Since that line is inside a Sub (as I wrote it)... ?

[nbench]

Rw = SummaryDates.Find(DailyDate).Row

Object variable or with block variable not set

The error above happens when I tried to run the code... Is it something obvious? Thanks

I edited the post above, here is the most recent question

[SamT]

I edited the post above, here is the most recent questionPlease don't do that.

To All: The Question before edit was regarding an error you get when a line of code is not inside a Sub or Function

The "With Block" Variable is the Object, (In this case a Worksheet,) named on the "With" Code line. The Name must be exactly as it is typed on the Sheet Tab, Check for extra spaces and spelling.

The Name in the Code is derived from the full name of the Month in the Date in Cell J1 on the Daily Reports Sheet, in the style "MonthName+Space+Summary". Check that then sheet name is properly designed and that the so named sheets exists.

In this Code, the Object Variable is "SummaryDates." and is being set to a range on the Worksheet with the name as generated above. Since that Range does exist on all sheets, the problem is probably with the so named sheet Name.

[SamT]

It is strongly suggested that all beginning Coders go to the VBA Menu Tools >> Options >> Editor Tab and check all CheckBoxes in the Code Settings Frame. On the General Tab, check the Break On Unhandled Errors CheckBox and both CheckBoxes in the Compile Frame.

The checks in the Code Settings Frame will force you to follow most recommended Best Practices. It will place Option Explicit at the top of all new Code Pages. You must manually place this on existing Code Pages.

Any time you complete a small section of code... Example

With Sheets("Daily Reports")
DailyDate = .Range("J1")
DailyValue = .Range("C30")
End With
... use the VBA Menu Debug >> Compile to check your code to that point.

The VBE (Visual Basic Editor) has several methods to help you.

Place the Cursor in or next to a Key Word and press F1 to see the Help on that word. BTW, do not use that Word as a variable name if there is help on it. doing so can confuse the VBA Compiler and break your code.
Pressing F5 will run the code, Pressing F8 will step thru the code.
Hovering the Mouse over a variable will display a ToolTip of the Value of the Variable


Right Clicking in the grey vertical bar on the left side of the Code Pane will let you:

Set Break Points, which will stop the running code just before it executes that line.
Set the Next Line to execute, even a precious line.
Set the line to stop at this time only. Breakpoints work until you clear them.


I often use the generic Variable "X" to test various assignments while troubleshooting. I will write an assignment to X, set a Break Point at X, run the code (pressing F5) to X, then press F8 and hover over X to see the value.

Dim X
X = Format(DailyDate, "mmmm") & " Summary"