macro not working on multiple computers

[mgiehm]

I created a macro and it works perfectly on my computer. However when I share it across the network and try to get it to work on other computers it stops working at when it hits this line of code

Dim i As Integer
Dim sFileName As String
 i = 1
 sFileName = MyCell & "-" & i & ".xls"

 Application.DisplayAlerts = False
 Do
 If Dir(sFileName) <> "" Then
 i = i + 1
 Let sFileName = MyCell & "-" & i & ".xls"
 Else
 End If
 Loop Until Dir(sFileName) = ""
 ActiveWorkbook.SaveAs sFileName, _
 FileFormat:=xlNormal, _
 Password:="", _
 WriteResPassword:="", _
 ReadOnlyRecommended:=False, _
 CreateBackup:=False
 
 Dim wbMyWB As Workbook
 Dim sXLSName As String
Dim sCSVName As String
 
  Set wbMyWB = ActiveWorkbook
  sXLSName = wbMyWB.Name
  sCSVName = ActiveWorkbook.Name
 
  Workbooks.Open "\\PC-1\Inbox\Steel Shop Card.xltm"
   Workbooks(sCSVName).Close
Exit Sub

The version of excel doesn't matter either. I have tried multiple versions and it literally just skips the above lines of code. I have no idea why but I really need to fix this issue. Any ideas?

 

[yogeshmaney]

please check if macros are enabled

 

[Ruddles]

Which line of code? Dim i As Integer?

Where's the rest of the code?

What's in MyCell?

Have you tried stepping through the code?

 

[mgiehm]

Sub SaveSheet()

 
 
 With ActiveWorkbook.BuiltinDocumentProperties
 .Item("Title") = Sheets("Steel Shop").Range("C2").Value
 .Item("Subject") = Sheets("Steel Shop").Range("N8").Value
 .Item("Author") = Sheets("Steel Shop").Range("C8").Value
 .Item("Comments") = Sheets("Steel Shop").Range("AI53").Value
 
 End With
 
 
 
 
 Dim currentDefaultFilePath As String
 
 currentDefaultFilePath = Application.DefaultFilePath
 
 Application.DefaultFilePath = "\\PC-1\Inbox\"
 'error trap
 On Error GoTo Etrap
 
 Call nameChng
 Call drwing
 Dim MyCell
 MyCell = Sheets("Steel Shop").Range("C5").Value
 
 'ask user to save
 
 If MsgBox("Save new workbook as " & MyCell & ".xls?", vbYesNo) = vbNo Then
 Exit Sub
 End If

 'check value of activecell
If MyCell = "" Then
 MsgBox "Please check the Job #", vbInformation
 Exit Sub
 End If

 
 
 'save activeworkbook as new workbook
 ActiveSheet.Shapes("CommandButton1").Visible = True
 ActiveSheet.Shapes("NetCardSave").Delete
 
 ActiveSheet.Shapes("CommandButton4").Visible = True
 
 ActiveSheet.Shapes("CommandButton5").Visible = False
 
 
 Call Sent
 
 Call Pwords
 
 
 Dim i As Integer
 Dim sFileName As String
 i = 1
 sFileName = MyCell & "-" & i & ".xls"
 
 Application.DisplayAlerts = False
 Do
 If Dir(sFileName) <> "" Then
 i = i + 1
 Let sFileName = MyCell & "-" & i & ".xls"
 Else
 End If
 Loop Until Dir(sFileName) = ""
 ActiveWorkbook.SaveAs sFileName, _
 FileFormat:=xlNormal, _
 Password:="", _
 WriteResPassword:="", _
 ReadOnlyRecommended:=False, _
 CreateBackup:=False
 
 
 Dim wbMyWB As Workbook
 Dim sXLSName As String
 Dim sCSVName As String
 
   Set wbMyWB = ActiveWorkbook
   sXLSName = wbMyWB.Name
   sCSVName = ActiveWorkbook.Name
 
   Workbooks.Open "\\PC-1\Inbox\Steel Shop Card.xltm"
   Workbooks(sCSVName).Close
 Exit Sub
 
 
 Etrap:
 Application.DefaultFilePath = currentDefaultFilePath
 Beep
 

 
Exit Sub
 

 
End Sub

There is the whole code for that module. I have stepped through it using f8 and it makes it all the way through until after call Pwords. Then skips over the saving of the file and exits the sub. Yes Macros are turned on. The only thing I haven't tried is putting it in it's own sub and calling to it but I wouldn't think that should matter in this instance. I appreciate the help for sure.

Edit: Just wanted to clarify that it skips from Dim i as Integer through the rest of the macro.

 

[Ruddles]

Is it erroring and jumping to the error handler?

Remove the On Error directive and run it again, allowing the error to report.

Add Option Explicit to the top of your module to catch any typos.

What's in MyCell?

 

[mgiehm]

I'm not at my work computer and wont be there til morning. So I will try it then and let you know. As it stands its just skipping right over it, it's not creating an error. I will add option explicit. MyCell is a 5 digit number that is based up on the users input in a cell. If they leave it blank it gives them a message stating they have to put something in it. It also becomes the name of the file so its easier for me to track later on.