VBA - Value of Named Range's In Cell's (Sheet Activate)

[glerwell]

Hi

I have two named ranges (month and year). Both ranges are only 1 cell, so they are named cells, if there is such a thing?

When I switch the sheet question I would like the current value of the ranges to appear in cells K29 and K30.

I have treid this code:

Private Sub Worksheet_Activate()
Range("K29").Value = Range("month").Value
Range("K30").Value = Range("year").Value
End Sub

However, I keep on getting a debug, why?

Cheers

 

[Norie]

How have you defined these names?

Are they worksheet or workbook level names?

 

[glerwell]

Workbook I think?

The scope is listed as "workbook" in the name manager.

To define the range, I highlighted the cell in question, typed the name in the namebox and pressed enter.

 

[glerwell]

Hi

Can anbody offer a solution to this?

Thanks