Join Worksheet function & VBA function

[Junarkar]

Hi,

I use below code to get Sheet Name;

Range("K1").Value = Split(ActiveSheet.Name, "_")(0)
Example Result - APS

And below code to in cell A1;

WorksheetName = "=CONCATENATE(""GC - "",Text(Today(),""MMMM DD""),"" - "",K1)"
Example Result - GC-July 07 - APS

Rather than taking the first result to Cell K1 and then use it in Concatenate function, is there a way to combine both formulas?

I want to know just out of curiosity and to learn more about VBA.

Regards
Junarkar

 

[Chihiro]

You can use...

Dim mystr as String
mystr = "GC - " & Format(Date, "mmmm dd") & " - "& Split(ActiveSheet.Name, "_")(0)

 

[Junarkar]

Thanks alot Chihiro