Basic VBA question re creating generic macro

[bstack]

Hi Guys
I'm no VBA coder but have used the 'Record Macro' tool to create a basic macro in my Personal Macro Workbook in Excel to tidy up some excel exports that are regularly dumped from one our doc management systems. The recorded script is as below. My basic question is this: I want to be able to run this macro across whatever workbook I have open regardless of the workbook/worksheet name but by default the 'Record Macro' tool appends the name of the workbook and worksheet that I was working in when I recorded the macro. Is there a generic syntax I can use instead?
Thanks!

    ActiveSheet.Range("Name of file[#All]").RemoveDuplicates Columns _
        :=Array(4, 5), Header:=xlYes
    ActiveWindow.LargeScroll ToRight:=1
    ActiveWorkbook.Worksheets("Name of worksheet").ListObjects( _
        "Name of file").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Name of worksheet").ListObjects( _
        "Name of file").Sort.SortFields.Add Key:=Range( _
        "Name of file[[#All],[Email]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Name of worksheet").ListObjects( _
        "Name of file").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[rlv01]

but by default the 'Record Macro' tool appends the name of the workbook and worksheet that I was working in when I recorded the macro. Is there a generic syntax I can use instead?

I see nothing in the code you posted that points at a specific workbook. It references the ActiveWorkbook, so it should run using whatever workbook you have open. But it looks for a specific worksheet called "Name of worksheet". You could change it to use whatever worksheet is active:

   ActiveSheet.Range("Name of file[#All]").RemoveDuplicates _
       Columns:=Array(4, 5), Header:=xlYes
    ActiveWindow.LargeScroll ToRight:=1
    ActiveSheet.ListObjects("Name of file").Sort.SortFields.Clear
    ActiveSheet.ListObjects("Name of file").Sort.SortFields.Add _
        Key:=Range("Name of file[[#All],[Email]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    
    With ActiveSheet.ListObjects("Name of file").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

but the active sheet would still need to contain the ListObject "Name of file" to sort

 

[bstack]

Thanks rlv01
I think the issue I'm having has to do with your comment that the active sheet would still need to contain the ListObject "Name of file".
The excel reports that are dumped from our doc management system all have different file names so I guess my question is what code can I use so that the macro will run on whatever the active worksheet is, irrespective of the name of the file. Does that make sense?
Cheers

 

[rlv01]

The purpose of your macro is to 1) Remove duplicates from a table (ListObject) named "Name of file" and 2)Sort that table. If your worksheet has that table ("Name of file") in it, and the table has a column then the modification to your code I posted earlier should work no matter the name of your [I]worksheet[/I] as long at it is the active worksheet. If the [I]table[/I] can be a lot of different names, then things get more complicated.

 

[bstack]

Ah, thanks rlv01. That was the knowledge gap - I hadn't realised that was the table name rather than the file name. I've now got a workaround. Cheers!

The purpose of your macro is to 1) Remove duplicates from a table (ListObject) named "Name of file" and 2)Sort that table. If your worksheet has that table ("Name of file") in it, and the table has a column then the modification to your code I posted earlier should work no matter the name of your [I]worksheet[/I] as long at it is the active worksheet. If the [I]table[/I] can be a lot of different names, then things get more complicated.[/QUOTE]