Trying to make a macro more efficient

[SDMaestro]

Hi All !

I have a piece of code written from the macro recorder which serves its purpose but runs quite slow as the data set increases, my VBA skills are limited so I'm wondering if or how the code below could be written to make the process more efficient ?

Basically what it will do is copy a list of filtered jobs (by type) from a table, then paste the jobs in a new sheet/table (Services), and remove any duplicate job numbers. The macro will run through and do the same process for a different job type and paste into another sheet/table (Rental)

Any help is much appreciated.

[I]Sub Refresh()[/I]
[I]'[/I]
[I]' Refresh Macro[/I]

[I]    Sheets("Monthly tables").Select[/I]
[I]    ActiveSheet.ListObjects("YTD").Range.AutoFilter Field:=8, Criteria1:= _[/I]
[I]        "Services"[/I]
[I]    Range("YTD[Job" & Chr(10) & "Number]").Select[/I]
[I]    Selection.SpecialCells(xlCellTypeVisible).Select[/I]
[I]    Selection.Copy[/I]
[I]    Sheets("Services").Select[/I]
[I]    Range("Services[Job Number]").Select[/I]
[I]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/I]
[I]        :=False, Transpose:=False[/I]
[I]    Application.CutCopyMode = False[/I]
[I]    ActiveSheet.Range("Services[Job Number]").RemoveDuplicates Columns:=1, Header:= _[/I]
[I]xlYes[/I]

[I]    Sheets("Rental").Select[/I]
[I]    Sheets("Monthly tables").Select[/I]
[I]    ActiveSheet.ListObjects("YTD").Range.AutoFilter Field:=8, Criteria1:= _[/I]
[I]        "Rental"[/I]
[I]    Range("YTD[Job" & Chr(10) & "Number]").Select[/I]
[I]    Selection.SpecialCells(xlCellTypeVisible).Select[/I]
[I]    Selection.Copy[/I]
[I]    Sheets("Rental").Select[/I]
[I]    Range("Rental[Job Number]").Select[/I]
[I]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/I]
[I]        :=False, Transpose:=False[/I]
[I]    Application.CutCopyMode = False[/I]
[I]    ActiveSheet.Range("Rental[Job Number]").RemoveDuplicates Columns:=1, Header:= _[/I]
[I]xlYes[/I]


[I]End Sub[/I]

 

[6StringJazzer]

This about as much as I could do just looking at the code. Seeing your file might provide insights for a more efficient design.

Sub Refresh()
'
' Refresh Macro


   Application.ScreenUpdating = False
   
   With Sheets("Monthly tables")
      .ListObjects("YTD").Range.AutoFilter Field:=8, _
                                           Criteria1:="Services"
      .Range("YTD[Job" & Chr(10) & "Number]").SpecialCells(xlCellTypeVisible).Copy
      Sheets("Services").Range("Services[Job Number]").PasteSpecial Paste:=xlPasteValues, _
                                                                    Operation:=xlNone, _
                                                                    SkipBlanks:=False, _
                                                                    Transpose:=False
      Application.CutCopyMode = False
      .Range("Services[Job Number]").RemoveDuplicates Columns:=1, _
                                                      Header:=xlYes
   End With
   
   With Sheets("Monthly tables")
      .ListObjects("YTD").Range.AutoFilter Field:=8, _
                                           Criteria1:="Rental"
      .Range("YTD[Job" & Chr(10) & "Number]").SpecialCells(xlCellTypeVisible).Copy
      Sheets("Rental").Range("Rental[Job Number]").PasteSpecial Paste:=xlPasteValues, _
                                                                Operation:=xlNone, _
                                                                SkipBlanks:=False, _
                                                                Transpose:=False
      Application.CutCopyMode = False
      .Range("Rental[Job Number]").RemoveDuplicates Columns:=1, Header:= _
      xlYes
   End With
   
   Application.ScreenUpdating = True




End Sub

You might want to review my write-up on macro recorder bloat.

 

[SDMaestro]

Thanks 6string ! I'll give this a go tomorrow & see if I can attach a template that might be easier to follow.

 

[SDMaestro]

The code you provided does seem to eliminate some waste from the macro recorder, appreciate it.

The link below is a skeleton of the template if it helps better illustrate what I am asking. Typically the job list would be much longer.

http://ge.tt/5gwXQqt2