Macro issue

[sodapop]

Hi,

Everyday I receive an open call list. My macro works great if I run ccleaner before hand. If I run the macro on different days it errors. I know it is hanging up on the title CRM Activity Report. Is there a way to compensate for the title changing to multiple copies? IE CRM Activity Report (2).





' Sort Data
ActiveWorkbook.Worksheets("CRM Activity Report").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CRM Activity Report").Sort.SortFields.Add Key:= _
Range("C2:C99"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal


I have been using excelisfun on youtube to learn more and have found out that I can clean up the macro a little bit IE range.


Thanks for the help.

 

[NdNoviceHlp]

U can trial this. HTH. Dave

Dim sht As Worksheet, ShtName As String
For Each sht In ThisWorkbook.Sheets
If Left(sht.Name, 19) = "CRM Activity Report" Then
ShtName = sht.Name
Exit For
End If
Next sht

' Sort Data
ActiveWorkbook.Worksheets(ShtName).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ShtName).Sort.SortFields.Add Key:= _
ActiveWorkbook.Worksheets(ShtName).Range("C2:C99"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[sodapop]

Thanks for the reply ND

 

[NdNoviceHlp]

You are welcome. I hope that U were able to resolve this? Dave