how to use dynamic variable insted of sheet name in vba

Question

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

Yes, you can :-)

Dominique
– Dominique

2018-07-30 11:36:13 +00:00
Commented Jul 30, 2018 at 11:36 — Dominique
– Dominique, Commented Jul 30, 2018 at 11:36

QHarr · Accepted Answer · 2018-07-30 11:45:19Z

3

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")

edited Jul 30, 2018 at 11:45

answered Jul 30, 2018 at 11:36

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Foxfire And Burns And Burns Over a year ago

... to previous day's closing balance.. I think OP wants the previous day, so s = Format$(DATE-1,"dd.mm.yy"), but I could be wrong. Anyways, your code is good :)

QHarr Over a year ago

Oops! Thanks for that.

user4039065 · Accepted Answer · 2018-07-30 11:40:53Z

0

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

answered Jul 30, 2018 at 11:40

user4039065

Collectives™ on Stack Overflow

how to use dynamic variable insted of sheet name in vba

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related