Getting sheetname that calls a macro.

[Ed Davis]

Is it possible to get the sheet name that a macro is called from.
I am using the code below to make a backup when the macro is called but it
can be called from any of 31 sheets.

Code =
ThisWorkbook.SaveCopyAs ThisWorkbook.Path &
"\Backup\Sales\Work\Change_Retails " & Format(Now, "dd-mm-yy hhmmss") &
".xls"
End of code

I would like to include the sheet name in the backup name

 

[Chris Watts]

ThisWorkbook.Name will give you the name of the active workbook.

Chris

 

[Billy Liddel]

dim strSH as string

strSH = activesheet.name

Code =
ThisWorkbook.SaveCopyAs ThisWorkbook.Path &
"\Backup\Sales\Work\Change_Retails\"& strSH & Format(Now, "dd-mm-yy
hhmmss") & ".xls"

Not tested

 

[Ed Davis]

Thank you
This works like a charm.