Macro crashes when run for a second time in Shared WB

kmandy

New Member
Joined
Aug 11, 2003
Messages
25
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.
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Troubleshooting begins with stepping through your code. Go to the beginning of your code, then run it with the F8 key to step through your code, carefully keeping track of the lines that produce no problem. As soon as you run into the problem, you must analyse that line, to locate and correct the problem.

If you are able to do the above, and repost the line where you find the problem, someone here is bound to be able to help you.
 

kmandy

New Member
Joined
Aug 11, 2003
Messages
25
Thanks RalphA. I have prviously tried this however I only get the problem once the WB becomes Shared, and I am unable to view a macro once it is shared. When I unshare the WB, I can step through the macro successfully many times. Hope someone may have an answer for this.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
If you don't get an answer, you might try this: Add a nmber of messages, numbered 1 to 6, say, to your code, then share it, and see what messages are returned. Then, you can unshare it again, and add a new message for each line, from the last one that appeared, so that you can determin what line is causing the original problem. a little drawn out, maybe, but you should be able to pinpoint the trougle-producing line.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,597
Messages
5,541,160
Members
410,543
Latest member
ExcelGlenn
Top