|First, a posting tip:|
Please click on the blue line at the end of this post and read the instructions on how to post code in this forum. Posting it per those instructions makes it a lot easier to read, as you'll see from my code below.
As for your question, your code picks up the date from a series of cells in the Master sheet. Since you want a formula in each sheet that refers to the previous sheet, you can pick up the name of previous sheet from the cell above the cell from which the then-current sheet name was taken.
Each time through the Do Loop your sheet names are picked up from Cells(inX, 1). Since inX is the Row that contains the current sheet name, inX-1 is the Row above it, which contains the name of the previous sheet.
The only problem is that for the sheet named from Row 1, inX-1 refers to Row 0, which will cause an error. Therefore I used the On Error instruction to allow the code to keep running when it refers to Row 0, since that will only happen once. Basically the code will see the error when it tries to access Cells(0,1) and skip that instruction, moving on to continue the code. As long as that is the only error, things will work fine (as in my testing) but if any other errors are detected, the code will ignore them also. That could be an issue if you alter the code and introduce any other errors.
I suggest that you try this code in a backup copy of your workbook, one without sheets that are already named for the Dates in the Master sheet.
Dim inX As Integer
Dim stName As String
'On Error used deal with Row 0 access
On Error Resume Next
'Initiate Row counter
inX = 1
'Do Loop to create sheets, insert formula
Do Until Cells(inX, 1).Text = ""
'Get Date from Master sheet
stName = Cells(inX, 1).Text
'Add Sheet and name is
ActiveSheet.Name = stName
'Insert formula, reference previous sheet
ActiveSheet.Range("H6").Formula = _
"='" & Sheets(Cells(inX - 1, 1).Text).Name & "'!I6"
'Increment Row counter
inX = inX + 1
I do have a question...
As far as I can tell, this code adds Sheets, named for a date, and will now contain 1 formula. That doesn't sound like much of a budget sheet. How are you getting the other information/formulas into the new sheets?
P.S. You might want to refer to this How To also. It will give you some basic trouble shooting techniques which might help you understand VBA code a little better.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.