[Inquiry-Macro/Invoke VBA] How to use Macro for sheet that has changed the name based on today date?

Hello, all

I’d like to activate Macro using Invoke VBA.
The problem is the target sheet name is going to be changed based on today date.
In this case, how to set macro…?


This target sheet name is always changed with today date
“HANA”+DateTime.Now.ToString("MMdd”)
image

Macro.txt is below
Sub Macro3()

’ Macro3 Macro


Columns(“F:F”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“F2”).Select
ActiveCell.FormulaR1C1 = “=VLOOKUP(RC4,Account_List!R5C6:R61C7,2,0)”
Range(“F2”).Select
Selection.AutoFill Destination:=Range(“F2:F47”)
Range(“F2:F47”).Select
Range(“L2”).Select
End Sub

Thank you for helps ~

Hi @Dorothy_lee

Try this

Sub Macro3()

shtName = “HANA” & format(now(),“mmdd”)
Sheets(shtName).select
Columns(“F:F”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“F2”).Select
ActiveCell.FormulaR1C1 = “=VLOOKUP(RC4,Account_List!R5C6:R61C7,2,0)”
Range(“F2”).Select
Selection.AutoFill Destination:=Range(“F2:F47”)
Range(“F2:F47”).Select
Range(“L2”).Select
End Sub

2 Likes

@prasath_S Hello,

Thank you very much! I’ve resolved the issue.
By the way, could you please advise for below config…?
I’d like to set similar setting for other sheet but seems like there is something wrong on script.
Could you please check yellow remarked part?


image
[Initial vlookup condition]
=IFERROR(VLOOKUP(C7,‘SMS0424’!A:C,3,0),0)

[txt.file]
Sub Macro6()

’ Macro6 Macro


shtName = format(now(),“mmdd”)
Sheets(shtName).select
Range(“E6”).Select
ActiveCell.FormulaR1C1 = “=IFERROR(VLOOKUP(RC3,‘“SMS”+format(now(),“mmdd”)’!C1:C3,3,0),0)”
Range(“E6”).Select
Selection.AutoFill Destination:=Range(“E6:E62”), Type:=xlFillDefault
Range(“E6:E62”).Select
ActiveCell.FormulaR1C1 = “=IFERROR(VLOOKUP(RC3,‘“SMS”+format(now(),“mmdd”)’!C1:C3,3,0),0)”
Range(“E10”).Select
End Sub

1 Like

Below should solve your problem.

Sub Macro6()

shtName = Format(Now(), "mmdd")
Range("E6").Select

lookUpSheet = "SMS" & shtName

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC3," & lookUpSheet & "!C1:C3,3,0),0)"
Range("E6").Select
Selection.AutoFill Destination:=Range("E6:E62"), Type:=xlFillDefault
Range("E6:E62").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC3," & lookUpSheet & "!C1:C3,3,0),0)"
Range("E10").Select

End Sub
1 Like

@Debasis_Nayak Thank you so much~!
It’s perfectly working.

1 Like

I’m glad :slight_smile: Please give me a like if you liked it.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.