Help with VBA script to create excel reference to a cell in a different worksheet

[twpiaget]

I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.

I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (http://www.mrexcel.com/forum/excel-...tions-how-refer-different-worksheet-cell.html) but I am not having any luck. Any suggestions? Thanks in advance.


Sub Budget_Rollover()
Dim i As Long
' Loop through all the worksheets, starting with the second sheet
For i = 2 To Worksheets.Count
Dim wsReference As String
Dim wsName As String
' Define the name of the previous expense cycle worksheet
wsName = Worksheets(i - 1).Name
' Construct a reference to the remaining budget cell in the previous expense cycle
wsReference = "='" & wsName & "!D6"
' Put the reference in the current expense cycle
'Worksheets(i).Range("D4").Value = "='" & reference & "!D6"
Worksheets(i).Range("D4").Value = wsReference
Next i
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

You are missing a single quote before the exclamation mark.

 

[twpiaget]

Ahhh! Syntax error, no wonder I have no hair.

Thanks.