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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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.
 

Forum statistics

Threads
1,141,163
Messages
5,704,667
Members
421,361
Latest member
AJPlant

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top