problem using range("A1").formula` 1

[cmz21]

Hello,
I have a workbook with multiple sheets. I am trying to reference a cell on sheet "Basic 07-05" from my summary sheet. The "Basic 07-05" sheet has a named range called BasicCount. I know that the cell value I need would be "='Basic 07-05'!BasicCount.
The problem I am running into is that when I name the sheet, I use the formula

xlSheet_Basic.Name = ("Basic Life " & Format(Now(), "mm-yy"))

in order to get a dynamic date on the sheet.

I tried to use the code

xlSheet_Summary.Range("B6").Formula = "="Basic Life " & Format(Now(), "mm-yy")'!BasicCount"

But I keep getting the compile error - Expected:End of statement.

I hope I am clear with my problem.

Does anyone have any idea what the syntax should be for this to work properly?

Thanks

 

[cmz21]

Sorry mistyped the formula -

xlSheet_Summary.Range("B6").Formula = "='"Basic Life " & Format(Now(), "mm-yy")'!BasicCount"

Thanks
cmz

 

[SkipVought]

Hi,

Your quotes...

xlSheet_Summary.Range("B6").Formula = "='Basic Life "  & Format(Now(), "mm-yy") & "'!BasicCount"

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH!}

 

[cmz21]

It is still giving me the error Compile Error:
Expected:End of Statement

 

[SkipVought]

Did you COPY 'n' PASTE my code?

It works PERFECTLY for me.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH!}

 

[cmz21]

I must have accidentall hit a key when I pasted.
Repasted it and it works perfectly.....Thanks Alot, I really appreciate it.

cmz