Excel not saving to network...

[p3tricka]

I am trying to to get an excel file saved onto a network from work. There are going to be 35 + users using an excel file on a sharepoint. The way it is suppose to work is... once the user opens the excel file on sharepoint a userform appears asking them to log in. Once logged in the excel file saves their file into a folder on the network and into their 'my documents' folder. The locations are CONST. There is a master document that goes into the folder and extracts the neccessary information - works fine if the file in the folder is updated. It is a production tool that keeps their production goals etc... The problem arises when they begin to continuously save their files into the specified folder every hour. (They tend to do it all at once) I don't know but when the number jumps from 10 to 35 they begin to lose their userforms and the macros do not begin to work.

What it should do is: once they have inputted their data it's only 2 numbers, they click save and it goes and saves the information onto the file in the back that was previously saved and their file in their 'my documents'. During the initial save there are no problems. There is a wait function that determine when to 'resend' the failed save. It does the code below basically twice after, upto 4 min, then the final attempt is to create a workbook, copy the information and save to the CONST saved location in the back.

The code goes like this.

Public Sub CONTINUOUS_SAVE()

On Error GoTo ERROR_ON_SAVE2

Dim COPIED_WORKBOOK2 As Excel.Workbook

ThisWorkbook.Sheets(1).Activate ' getting the information
Sheets(1).Range("C9:D26").Select
Selection.Copy
Application.CutCopyMode = False

Excel.Application.DisplayAlerts = False

Set COPIED_WORKBOOK2 = Excel.Workbooks.Open(Filename:=SAVE_TO_BACK & FIRST_NAME & " " & LAST_NAME & " COPY.xlsm") 'opening the saved file in the back * sometimes this is where it fails...

COPIED_WORKBOOK2.Sheets(1).Activate

With COPIED_WORKBOOK2 ' pasting the info to the open excel file in the back
Range("C9:D26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With

COPIED_WORKBOOK2.SaveAs Filename:=SAVE_TO_BACK & FIRST_NAME & " " & LAST_NAME & " COPY.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False ' re-saving --> *FAILS HERE
COPIED_WORKBOOK2.Close
Set COPIED_WORKBOOK2 = Nothing
Excel.Application.DisplayAlerts = True

ThisWorkbook.Activate

ActiveSheet.Buttons.Visible = True
ticker_frm.SAVE_CMD.Enabled = True

Exit Sub
ERROR_ON_SAVE2:

ActiveSheet.Buttons.Visible = False ' so they cannot continue pressing save
ticker_frm.SAVE_CMD.Enabled = False


'If COPIED_WORKBOOK2 <> Nothing Then COPIED_WORKBOOK2.Close ' this doesn't seem to work - need to set as nothing if set file was complete

Set COPIED_WORKBOOK2 = Nothing

Excel.Application.DisplayAlerts = True

Retry = False
Mid_POINT = False
Last_Attempt = True
Do_NOT_KNOW_WHATELSE = False

Call Wwaiting
End Sub

Though it works for the majority of the day... 10 might start but only 2 will be left and that is quite annoying. I can run it with out any issues on my computer, but when it goes out to my associates it begins to deteriate and they begin to fall like flies. Sometimes I see they have the "COPY" on their desktop and the original is lost. If it fails it should de-activate the save buttons on the sheet and on the userform. I am beginning to wonder if it is a network issue because when the user's number, amount saving into the folder, jumps up, problems begin to pop up. Error messages from '1004 to '91 to '7 have been seen. But I am not able to de-bug them immediately to see where it failed.

I hope that made sense. Any help would be greatly appreciated.
Any help would be appreciated.

[p3tricka]

I think there is a simpler way... is it possible to link workbooks from a personnal drive to a shared network?