Macro working in background ie not on activesheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a few macros that I am working on that move between different sheets.
I am trying to have them operate in the background rather than on the
ActiveSheet, but I can't figure out the syntax.

' Macro3 Macro
' Macro recorded 5/23/2005 by A0jxvzz
'declarations
Dim MyFromColumn As Integer
Dim MyToColumn As Integer
Dim MyFromStartCell As String
Dim MyFromEndCell As String
Dim MyToStartCell As String
Dim MyToEndCell As String
Dim MyNamedRange As Range
Dim MyRangeName(1 To 11) As String
Dim Counter


For Counter = 1 To 11

MyRangeName(Counter) = "ComplntCd" & Counter
Next



'Clear Area
Sheets("Data").Select
Range("AA1:AZ1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'AutoFilter
For MyFromColumn = 1 To 11

MyToColumn = MyFromColumn + 26

MyFromStartCell = Cells(1, MyFromColumn).Address
MyFromEndCell = Cells(65536, MyFromColumn).Address
MyToStartCell = Cells(1, MyFromColumn + 26).Address
MyToEndCell = Cells(65536, MyFromColumn + 26).Address
Range(MyFromStartCell & ":" & MyFromEndCell).AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Range(MyToStartCell), Unique:=True
'Sort
Range(MyToStartCell & ":" & MyToEndCell).Select
Selection.Sort Key1:=Cells(2, MyToColumn), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Define Name
Set MyNamedRange = Range(MyToStartCell & ":" & MyToEndCell)
ActiveWorkbook.Names.Add Name:=MyRangeName(MyFromColumn),
RefersToR1C1:=MyNamedRange

Next

'Move Away from ActiveSheet
Sheets("Front").Select

End Sub
 
you could turn off the screenupdating while the macro is running, then turn
it back on (set it to True) just before the macro ends.

application.screenupdating = false
 
Back
Top