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”)
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 ~
prasath_S
(prasath S)
April 23, 2021, 6:06pm
2
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?
[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
Dorothy_lee:
=IFERROR(VLOOKUP(C7,‘SMS0424’!A:C,3,0),0)
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 Please give me a like if you liked it.
1 Like
system
(system)
Closed
April 28, 2021, 4:35pm
7
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.