Auto updating of worksheets in an Excel Workbook

[DPL50]

Hi, I have been struggling to find a way to update a summary sheet with daily information. It seems so simple but I haven't been able to use Excel commands. I tried IF with circular references, but it didn't work, so I guess I will need a macro, and that's where I come completely unstuck - I have no macro experience. I have trawled the forum but can't find any similar question.

I would be really grateful for any suggestions as this has been frustrating me. I am using 2013 version of Excel on Windows 10. I have tried to detail what I need below.

Thanks.

Two Sheets – Daily,Summary.
Therequirement: To be able to Paste Values in Summary with data from Dailyduring each day by use of a button labelled “Update Summary” and to reset Dailyfor the next day if a positive response to a further message is received.
Thedetails:
1. To PasteValues to the row in Summary thathas the same date in Column A as cell C5 in Daily,as follows (where n is the row number):


Daily Worksheet Cells
Summary Worksheet Cells


R70 – X70
AQn - AWn


E76
Nn


F76
Tn


L76
AFn


Q76
AMn












2. Display themessage “Do you want to reset Daily? Y / N”
3. If N isselected go to step 4.
If Y is selected Clear Contents inDaily as follows:


Daily Worksheet Cells


C9 – L23


C25 – L39


C41 – L58


C60 – C69












Without affecting any of the data onSummary.
4. End the macro.

[SamT]

Without affecting any of the data on Summary.
4. End the macro.

Steps 2, 3, & 4 look to be totally separate from Step 1. Do you want the step #2 question before the data in step #1 is placed in Summary?


Option Explicit

Public Sub UpdateSummary()
Dim Daily As Worksheet
Dim Summary As Worksheet
Dim DailyDate As String
Dim Found As Range
Dim ClearDaily As Long
Dim Rw As Long

'Move the next three lines to where needed in Macro Steps
'Macro Step 2
ClearDaily = MsgBox("Do you want to clear the Daily sheet", vbYesNoCancel)
If ClearDaily = vbYes Then GoTo EndOfSub

Set Daily = Sheets("Daily")
Set Summary = Sheets("Summary")
DailyDate = Daily.Range("C5")

'Macro step #1
Set Found = Summary.Range("A:A").Find(DailyDate)
If Not Found Is Nothing Then
Rw = CStr(Found.Row)
With Summary
.Range("AQ" & Rw & ":AW" & Rw) = Daily.Range("R70:X70")
.Range("N" & Rw) = Daily.Range("E76")
.Range("T" & Rw) = Daily.Range("F76")
.Range("AF" & Rw) = Daily.Range("L76")
.Range("AM" & Rw) = Daily.Range("Q76")
End With
End If

'Macro Step #3
With Daily
.Range("C9:L23").ClearContents
.Range("C25:L39").ClearContents
.Range("C41:L58").ClearContents
.Range("C60:C69").ClearContents
End With

EndOfSub:
End Sub

[DPL50]

Hi SamT. thanks for the quick response.

No, step 1 has to come first as clearing that data will affect the data being copied to the summary sheet.

So, if I move the Step 2 lines
'Move the next three lines to where needed in Macro Steps
'Macro Step 2
ClearDaily = MsgBox("Do you want to clear the Daily sheet", vbYesNoCancel)
If ClearDaily = vbYes Then GoTo EndOfSub

To immediately before
'Macro Step #3

it should be ok?

Thanks.

[DPL50]

Hi Sam T,

I have moved the step 2 code after step 1, but the only thing that is working is clearing the data in Daily. It doesn't update Summary with anything. Have I moved the code to the wrong place?

Thanks.

[SamT]

It sounds like you moved the code correctly.

Declare (dim) Rw As String, OR, delete Cstr from Rw =CStr(...)

Make sure I spelled all the sheet names correctly.

Change

DailyDate = Daily.Range("C5") to

DailyDate = Daily.Range("C5").Text

Use the Menu:=Debug >> Compile VBA Project to find many errors.

With the cursor inside the Sub, Press F8 to step thru the code. You can look at the Worksheets while pressing F8. This will find 99.999% of all other errors, if any.



But the proof is still in the pudding. Until you have used the code for quite a while, it is possible to come across a worksheet that is so unusual that it fails.

One final trick:

Dim X
With Summary
.Range("AQ" & Rw & ":AW" & Rw) = Daily.Range("R70:X70")
X = Daily.Range("R70:X70")
.Range("N" & Rw) = Daily.Range("E76")
X = Daily.Range("E76")
.Range("T" & Rw) = Daily.Range("F76")
X = Daily.Range("F76"")
.Range("AF" & Rw) = Daily.Range("L76")
X = Daily.Range("L76")
.Range("AM" & Rw) = Daily.Range("Q76")
X = Daily.Range("Q76")
End With
End If

When you are stepping thru the code with F8, you will see the lines of code Highlight yellow. That highlighted line is the next line to execute when you press F8.

Step down to the "With Summary" line. Hover the mouse over one of the X'es. When an X line loses it's highlighting, A Tool Tip will appear under the mouse giving the value of the Range on that line of code. If the line is working.

[DPL50]

Hi Sam T,

Many thanks for your help.

I'll make the changes on Monday and test it during the week. I'll come back to post an update at th eend of the week.

Thanks again.

[DPL50]

Hi Sam T,

I made the changes you suggested and unfortunately the result was the same.

Stepping through module 1 I got the following:

DailyRate 23/05/16

If NotFound is Nothing Then
Cursor on Found gave: Found=Nothing
Cursor on Nothing gave: Nothing=Nothing

Rw=CStr(Found.Row)
Cursor on Rw gave: Rw = 0
Cursor on CStr gave: CStr(Found.Row) = <Object variable With block variable not set>

With Summary
.Range("AQ" & Rw & ":AW" & Rw) = Daily.Range("R70:X70")
Cursor on .Range gave: .Range("AQ" & Rw & ":AW" ...=<Object variable With block variable not set>

X = Daily.Range("R70:X70")
Cursor on X gave: X = Daily.Range("R70:X70") = Type Mismatch

.Range("N" & Rw) = DailyMealPlanner.Range("E76")
Cursor on .Range gave:=<Object variable With block variable not set>
Cursor on Daily.Range gave: Daily.Range="41Blue42Black38Red... (this is correct)

It is the same for the rest of the rows in Step 1

Steps 2 and 3 are working fine.

Thanks.

[SamT]

If NotFound is Nothing Then
Cursor on Found gave: Found=NothingThat means it did not find the DailyDate in Column "A" on the Summary sheet.

Make sure that the two dates are in identical Format.
You can reformat the DailyDate String with

DailyDate = Format(Daily.Range("C5"), "mm/dd/yy") 'or similar

[DPL50]

Hi, I'm still not getting the date found. I inserted: Else
MsgBox DailyDate & " not found" before the With Summary Line.
It returned the message box with today's date right next to the row on the Summary Sheet with today's date. ie the date it should have found in exactly the same format. I tried changing the range to the actual cell and stepped through the macro. It still didn't find it, but when I ran the cursor over each line the dailydate and summarydate both had 27/05/2016 in the same format.