0

The macro works perfectly on mine Excel365 but it crashes when my colleague runs it on his computer (also Excel365). Can you please tell me what I'm doing wrong? I'd like to provide the tool working for everyone.

Here's problematic fragment of the code.

'Optimalization activated
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

On Error GoTo ErrorHandler


Dim myValue As String
Dim wb As Workbook

'Welcome Prompt/clipboard thingy

    MS = "Hello! Before we start, please prepare Customer ID. We'll need it later" & vbNewLine & vbNewLine
    MS = MS & "CLICK ON THE 'OK' BUTTON ONCE COPIED"
    MsgBox MS, vbOKOnly, "Welcome - let's prepare Customer ID"


'New excel
    Workbooks.Add
    Set wb = ActiveWorkbook

'Data from server - copy & close
    Workbooks.Open ("\\THE_FILE_FROM_SERVER.xlsx")

    Workbooks("THE_FILE_FROM_SERVER").Sheets("Sheet1").Activate

    Sheets("Sheet1").Cells.Copy Destination:=wb.Sheets("Sheet1").Range("A1")

    Workbooks("THE_FILE_FROM_SERVER.XLSX").Close SaveChanges:=False

'New Workbook magic
    wb.Sheets("Sheet1").Activate

'Column cleanup
    Columns("A:C").Select
        Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
        Selection.Delete Shift:=xlToLeft
    Columns("D:F").Select
        Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
        Selection.Delete Shift:=xlToLeft
    Columns("L:Y").Select
        Selection.Delete Shift:=xlToLeft
    Columns("M:R").Select
        Selection.Delete Shift:=xlToLeft
    Columns("N:AT").Select
        Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    
'Borders added
    Columns("A:M").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    Rows("1:1").Select
    Selection.AutoFilter
    Range("A1").Select
    
    
'Top row freeze
    wb.Sheets("Sheet1").Activate
    With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

'Renaming
    wb.Sheets("Sheet1").Activate
    ActiveSheet.Name = "Open Orders Status" & " " & Format(Date, "yyyy/mm/dd")

The moment it crashes only on some Excels:

'Top row freeze
        wb.Sheets("Sheet1").Activate

Thank you in advance for taking a time to review my lines.

Totti
  • 11
  • 1
  • 1
    Would guess that the default sheet name is different (it is depending on the language ) and therefore "Sheet1" doesn't exist. Either use the index (`wb.Sheets(1)`) or rename the sheet when you create the workbook. And, of course, obligatory link to https://stackoverflow.com/a/10717999/7599798 – FunThomas Oct 21 '22 at 09:53
  • Thank you @FunThomas. I'm pretty sure both Excels are in the same language setting. But I'll try indexing and let you know about the effect. About obligatory link - much appreciated hint to improve this aspect. I'm totally self-taught coding impostor and I'm fully aware of this. Have a good one. – Totti Oct 21 '22 at 13:58
  • @FunThomas - I've tried indexing the sheet instead of the name and the problem remains (works on mine, crashes on my friends' computers). Also I can confirm that every environment checked was English version of Office 365 inside one company. Do you have any other idea, please? – Totti Oct 27 '22 at 13:18
  • Are you sure that the `Activate`-statement throws the error? The error message you wrote in the header ("Invalid name...") suggest it's the next line. And there is the next possible source: `Format(Date, "yyyy/mm/dd")` will format the date according to the regional settings - to be precise it will replace the slash with the regional date separator character. This may be a dot or a slash (or maybe some other characters). A slash is not allowed in a sheet name. – FunThomas Oct 27 '22 at 14:09
  • @FunThomas - indeed that's the cause of the error. On my version **/** was replaced by **-** automatically but for some reason it didn't worked for different Excels. Thank you for your help. I truly appreciate this. – Totti Oct 28 '22 at 12:41

0 Answers0