CODEWITHSUNDEEP
excelvbadynamic-variables
R.Ash
R.Ash

Reputation: 11

how to use dynamic variable insted of sheet name in vba

Every day i need to link today's opening balance to previous day's closing balance ,i have recorded a macro like this 

    Range("D5").Select
ActiveCell.FormulaR1C1 = "='27.07.18'!RC[6]"
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:E5"), Type:=xlFillDefault
Range("D5:E5").Select
Selection.AutoFill Destination:=Range("D5:E86"), Type:=xlFillDefault
Range("D5:E86").Select
Range("D82").Select

This does the work but every day i have to chance a file name to today's date (as date is my sheet name )

Can i Use a Variable Insted Of Sheet name in FormulaR1C1

Upvotes: 1

Views: 3225

Answers (2)

QHarr
QHarr

Reputation: 84465

Try

Dim s As String
s = Format$(DATE,"dd.mm.yy")
ActiveCell.FormulaR1C1 = "='" & s &"'!RC[6]"

Assumes that the sheet already exists with this name. Otherwise first you need

Sheets.Add
Activesheet.Name = s

And you need to have the ActiveCell actually reference the original cell as the newly added sheet would have shifted this.

*Note: If previous day please use s = Format$(DATE-1,"dd.mm.yy")

Upvotes: 3

user4039065
user4039065

Reputation:

You could base it on a string var.

dim ws1 as string, ws2 as string

ws1 = "26.07.18"
ws2 = "27.07.18"

with worksheets(ws2)
    .Range("D5:E86").FormulaR1C1 = "='" & ws1 & "'!RC[6]"
end with

Perhaps you are adding a worksheet to the end of the queue every day.

with worksheets(worksheets.count)
    .Range("D5:E86").FormulaR1C1 = _
        "='" & worksheets(worksheets.count-1).name & "'!RC[6]"
end with

Upvotes: 0

Related Questions