I have created a macro that works fine when not shared. I can run this as many times as I like. However once I share the WB it allows the macro to run once but if I try to run it again it freezes immediately and I have to Ctrl Alt Del out of excel.
The code is basically using a autofilter on 3 separate sheets and bringing the data into another sheet, then sorting and removing duplicate entries.
Any Ideas?
Many thanks for your help in advance.
My code is below.
The code is basically using a autofilter on 3 separate sheets and bringing the data into another sheet, then sorting and removing duplicate entries.
Any Ideas?
Many thanks for your help in advance.
My code is below.
Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Temp").Select
Range("i1:n100").Select
Selection.Clear
Sheets("SIVS").Select
Cells.Select
Selection.AutoFilter Field:=12, Criteria1:="COUN"
Range("D:D,K:K").Select
Selection.Copy
Sheets("Temp").Select
Range("i:j").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("ORIG").Select
Cells.Select
Selection.AutoFilter Field:=12, Criteria1:="COUN"
Range("D:D,K:K").Select
Selection.Copy
Sheets("Temp").Select
Range("k:l").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("TANGO").Select
Cells.Select
Selection.AutoFilter Field:=12, Criteria1:="COUN"
Range("D:D,K:K").Select
Selection.Copy
Sheets("Temp").Select
Range("m:n").Select
Selection.PasteSpecial Paste:=xlValues
Range("A2:B100").Select
Selection.Clear
With Sheets("temp")
.Range("i2:j100").Copy Sheets("temp").Range("a" & Rows.Count).End(xlUp).Offset(1)
.Range("k2:l100").Copy Sheets("temp").Range("a" & Rows.Count).End(xlUp).Offset(1)
.Range("m2:n100").Copy Sheets("temp").Range("a" & Rows.Count).End(xlUp).Offset(1)
End With
Range("A2:B100").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Calculation = xlCalculationAutomatic
Range("a2:c100").Select
Selection.Copy
Sheets("CW").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="DUP"
Rows("2:100").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter
Range("a2").Select
Sheets("orig").Select
ActiveSheet.AutoFilterMode = False
Sheets("sivs").Select
ActiveSheet.AutoFilterMode = False
Sheets("tango").Select
ActiveSheet.AutoFilterMode = False
Sheets("cw").Select
Application.ScreenUpdating = True