Using an IF function inside a macro to generate another macro

[bleached88]

Hi guys,
this is quite a complex problem and seeing as I haven't used vba since I was in school I might be a little bit out of my league. I've designed two spreadsheets to monitor task counting where I work, one workbook for a daily analysis and another for monthly which we will be pasting values only from the daily workbook into. I'm trying to protect these workbooks so that nobody can delete any of the formulas, so I've been creating buttons and writing macros that unprotect the data, copy information across, then reprotect the data afterwards. These work well but the issue I've come across is this; there are buttons next to every day on the monthly log that basically do the same thing, copy info from the daily tracker into the relevant block of cells next to each button, one for each day so 31 in all. However, if someone were to press the wrong button next to the wrong day then the info copied over from the daily spreadsheet will replace data already in the monthly tracker, which then can't be undone. My idea is to add an extra layer of validation into the macro of the button that checks the date on the monthly tracker vs the date on the daily tracker before executing the copying of the data. This is where it gets complex because I'm comparing the values of two cells in two separate workbooks to generate another macro. In my head the idea would be something like this in simple terms:

Sub check_date()
If daily.range("B5") = monthly.range("B225")
Then run"copy_data"
end sub

I realise there's probably a lot more to it than that because I'm comparing values from two different workbooks. On the daily spreadsheet, the date in the cell is generated by =TODAY() so I don't know if this will affect anything? As I said I've already written the macro for "copy_data" and it works perfectly. Any help would be much appreciated as I'm a little bit out of my league thanks in advance

 

[JoeMo]

If both workbooks are open there's no problem comparing a cell in one workbook to a cell in the second provided the cell references are properly qualified. Why not try it? Post back if you have a problem indicating which line in your code is responsible and what the error message is.

 

[bleached88]

Thanks for your reply JoeMo
I've been trying to write the code but the correct syntax eludes me. I'm unsure of how to write the script as it's been nearly 15 years since I learnt vba at school and searching the net for this problem has given lots of different methods of comparing cells in different workbooks that had just completely baffled me. Any chance of refreshing my memory? The way I wrote the code in my previous post is how I imagine it to look but I tried writing it yesterday at work and I won't be able to try and edit it until I go back to work this afternoon. Thanks again

 

[Michael M]

What is daily.range and monthly.range

If daily.range("B5") = monthly.range("B225")

Are they workbook names,Sheet names, named ranges ???
You will need to refer to WorkBook name, Sheet name AND cell reference !!!

 

[bleached88]

Thanks Michael, I thought I might have to go along that line. So the code would be more like:

Sub check_data()
If Workbooks.("Daily Workbook.xls") Sheets.("Daily Log") Range.("B5") = Workbooks.("Monthly Workbook.xls") Sheets.("Monthly Log") Range.("B225")
Then run "copy_data"
end sub

Is that more like correct syntax?

 

[JoeMo]

Like this:

Sub check_data()
If Workbooks("Daily Workbook.xls").Sheets("Daily Log").Range("B5") = _
    Workbooks("Monthly Workbook.xls").Sheets("Monthly Log").Range("B225") Then
    Call copy_data 'Name of your macro here
Else
    'Do something else
End If
End Sub