This a very basic solution (maybe I'm missing the full point of the question). ActiveSheet.Name will RETURN the string of the current tab name (and will reflect any future changes by the user). I just call the active sheet, set the variable and then use it as the Worksheets' object. Here I'm retrieving data from a table to set up a report for a division. This macro will work on any sheet in my workbook that is formatted for the same filter (criteria and copytorange) - each division gets their own sheet and can alter the criteria and update using this single macro.
Dim currRPT As String
ActiveSheet.Select
currRPT = (ActiveSheet.Name)
Range("A6").Select
Selection.RemoveSubtotal
Selection.AutoFilter
Range("PipeData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("C1:D2"), CopyToRange:=Range("A6:L9"), Unique:=True
Worksheets(currRPT).AutoFilter.Sort.SortFields.Clear
Worksheets(currRPT).AutoFilter.Sort.SortFields.Add Key:= _
Range("C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal