Application-defined or Object-defined error when dealing with variables for sheet and ranges scoped to sheet level

[lmoseley7]

I have a workbook where sheets are added with the date as the sheet name (e.g. 052615, 052215, etc.) Each sheet has a range named PV_1. Each sheet is a copy of the previous day's sheet and includes two macro buttons. I've set this up for some co-workers and one person can't ever run the macros but most have not had issues. I came across some issues today that don't make sense to me.

Today I'm working on the 052615 tab. The macro uses formulas within the sheet to determine what the previous sheet's name is (it will be the last workday before the current day). In this case we are looking at sheet 052215. When I run the macro, it initially goes to the prior day's sheet and replaces the range PV_1's formulas with values. Then new numbers are pulled into the workbook which then feed the current page's formulas. The error comes up when I'm trying to replace the formulas on the prior day's sheet with values. The code I'm using is as follows:

Sub Pull_Balances()
'
Dim baseSheet As Worksheet
Dim BDate As String
Dim NDate As String

    Application.ScreenUpdating = False
'The user should be on the most current date's worksheet when this
'macro is run.
    Set baseSheet = ActiveSheet
    baseSheet.Activate
'Setting variables within the current sheet
    BDate = Range("BalDate").Value
    NDate = Range("NextDay").Value
'Removing VLOOKUP formula from balance range of previous day's worksheet
    Sheets(BDate).Range("PV_1").Value = Sheets(BDate).Range("PV_1").Value

When I'm on the most recent sheet and run the macro, I get an Application-defined/object-defined error from the last line I included above. If, however, I run the macro from the previous day's sheet, which the current day's sheet is a copy of, I don't get the error.

Does anyone have any guess as to why I am getting this error? Are there any suggestions for correction of my code? On a less important note, am I confusing Excel by copying sheets that include macro buttons?

 

[lmoseley7]

I should add that the ranges BalDate and NextDay exist in everysheet and are set to the worksheet scope level. The contents of these cells are, for example, 052215 and 052615 respectively in my example, and these are the names of existing sheets within the workbook. I feel confident that these amounts are working correctly because, as I mentioned before, the macro works if I am not using the most current sheet.

 

[lmoseley7]

I may have found a correction, although I don't understand why it is working. I was looking at other code which I put together recently and noticed that I was referring to named ranges differently than in my previously attached code. Instead of using ".Range("PV_1").Value" I lose the .Range and the () and replace them with [] and it works. Will someone explain why the change to brackets works? If there are other suggestions for other changes or explanations why something is working or not, I'm still open to them from anyone with the time to share.

Thanks,

Larry

 

[lmoseley7]

Despite my apparent success at getting this macro to run, I'm not sure why the change I made was needed or why it worked intermittently using the parentheses and quotes. Will anyone point me in the right direction?