Excel not responding when run macro on the shared workbook

[vanilz]

Need help on below.

Basically, I want to combine all the data from worksheets into one worksheet. And this workbook is shared and will be used by multiple users at the same time and save the files before combine. However, when run the macro, it is not responding.


Code:

Sub Combine()    Dim J As Integer
    Dim sourceSheet As Worksheet
    
    'Disable Screen Updating - stop screen flickering
    Application.ScreenUpdating = False
    
    'set the Masterlist sheet as active
    Set sourceSheet = ActiveSheet
    
   
    'delete previous rows
    Sheets(1).Activate
    Rows("5:" & Rows.Count).Delete
    
    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A5")


    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A2").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets


        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select


        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A9999").End(xlUp)(2)
    Next
       
    Sheets(1).Range("A5").AutoFilter
    Sheets(1).Columns.AutoFit
    
    ' return to Masterlist sheet
    Call sourceSheet.Activate
    
    'Enable Screen Updating and Events
    Application.ScreenUpdating = True
End Sub

 

[vanilz]

It is running on Excel 2010

Need help on below.

Basically, I want to combine all the data from worksheets into one worksheet. And this workbook is shared and will be used by multiple users at the same time and save the files before combine. However, when run the macro, it is not responding.


Code:

Sub Combine()    Dim J As Integer
    Dim sourceSheet As Worksheet
    
    'Disable Screen Updating - stop screen flickering
    Application.ScreenUpdating = False
    
    'set the Masterlist sheet as active
    Set sourceSheet = ActiveSheet
    
   
    'delete previous rows
    Sheets(1).Activate
    Rows("5:" & Rows.Count).Delete
    
    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A5")


    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A2").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets


        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select


        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A9999").End(xlUp)(2)
    Next
       
    Sheets(1).Range("A5").AutoFilter
    Sheets(1).Columns.AutoFit
    
    ' return to Masterlist sheet
    Call sourceSheet.Activate
    
    'Enable Screen Updating and Events
    Application.ScreenUpdating = True
End Sub

 

[vanilz]

Please help with the code.. How to make it work to run in Shared workbook.

I added macro to unshared it from the beginning and run the macro and thereafter shared the workbook again.

Tried to add some contents before run the macro but the data is not saved. When the second person who open the workbook, it doesn't show as SHARED and no prompt to open read-only. Also, the data added is not available.

Could you please help with the macro? I have been struggling how to make it work.. at least it can be combine data, on shared workbook and saved multi user changes.

Unshared and shared code:

If ActiveWorkbook.MultiUserEditing Then
        Application.DisplayAlerts = False
        ActiveWorkbook.ExclusiveAccess
        Application.DisplayAlerts = True
    End If

If Not ActiveWorkbook.MultiUserEditing Then
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
        Application.DisplayAlerts = True
    End If