Excel macro pre 2016

[brutusbeats]

I have an Excel macro which works on Excel pre 2016 but not 2016 onwards. Does anyone know why this would no longer work on the newer versions?

Sub fun_create_sheets()
Dim sheetsNumber As Integer
Dim riskNumber As Integer

Dim I As Integer
Dim j As Integer
Dim k As Integer
Dim sheetCurrent As Integer
Dim workingSh As Variant
Dim temp As String
Dim rng As Range
Dim UsedRange As Range




    sheetsNumber = Sheets("Results").Range("E5").Value
   
    Sheets("Part C (0)").Visible = True
    Sheets("Part C (0)").Activate

    ' Delete old sheets containign "Part C("
    For Each sh In ActiveWorkbook.Worksheets
        'MsgBox (sh.Name)
        If InStr(sh.Name, "Part C (") Then
            If InStr(sh.Name, "Part C (0)") Or InStr(sh.Name, "Part C (Sign)") Then
                'MsgBox ("no borrar")
            Else
                'MsgBox ("borrar")
                Application.DisplayAlerts = False
                sh.Delete
                Application.DisplayAlerts = True
            End If
        End If
    Next sh
   
   

    ' Create new  Part C sheets if rquired
    If sheetsNumber >= 1 Then
       For I = 1 To sheetsNumber
        Sheets("Part C (0)").Select
        Sheets("Part C (0)").Copy Before:=Sheets(1)
        Sheets("Part C (" + CStr(0 + I) + ")").Select
        Sheets("Part C (" + CStr(0 + I) + ")").Move Before:=Sheets("Part C (Sign)")
    Next I
    End If
    Sheets("Part C (0)").Visible = False
   
    'Populate sheets
    riskNumber = Sheets("Results").Range("E3").Value
    sheetCurrent = 1
   
    For I = 1 To riskNumber
    Sheets("Part C (" + CStr(sheetCurrent) + ")").Select
   
        For j = 1 To 6
            'MsgBox (i)
            Application.CutCopyMode = False
            temp = "F" + CStr(1 + I)
            Sheets("Results").Range(temp).Copy
            temp = "C" + CStr(3 + j * 9)
           
            Range("E12:K20").Select
            Sheets("Part C (" + CStr(sheetCurrent) + ")").Select
            ActiveSheet.Range(temp).Select
            'MsgBox ("D")
            ActiveSheet.Paste
           
            temp = "G" + CStr(1 + I)
            Sheets("Results").Range(temp).Copy
            temp = "E" + CStr(3 + j * 9)
            Sheets("Part C (" + CStr(sheetCurrent) + ")").Select
            ActiveSheet.Range(temp).Select
            ActiveSheet.Paste
            I = I + 1
        Next j
        I = I - 1
       
        For k = 1 To 6
            temp = "AG" + CStr(3 + 9 * k)
            Set rng = Sheets("Part C (" + CStr(sheetCurrent) + ")").Range(temp)
            'MsgBox (rng.Address)
            rng.Formula = rng.Value
            rng.Value = rng.Value
        Next k
        sheetCurrent = sheetCurrent + 1
    Next I
           
   
End Sub

 

[Joe4]

it's a file from work which others have and they work on their laptops as they have older versions of Excel but doesn't work on any of the new laptops

It sounds like maybe the file has become corrupted.
Try creating a new workbook from scratch, and copy over all the VBA code and see if that works.

 

[brutusbeats]

Do you have other VBA code in this workbook?
What does that look like ?

Instead of chasing our tail and guessing what else you have going on in this workbook, it would probably be best if you could upload a copy of it to a file sharing site and provide the link to it here, so we can download it and debug it for ourselves. Just be sure to remove any sensitive data first.

Good idea, what site do you suggest?

 

[MARK858]

@brutusbeats can you try saving the file as an .xlsb file, then opening it and running the code again. What error do you get with that file?

Good idea, what site do you suggest?

Try www.dropbox.com or www.box.com (both are free for non-business users)
Remember to mark it for sharing and use the link it then provides, and to sanitize the data before uploading

 

[brutusbeats]

@brutusbeats can you try saving the file as an .xlsb file, then opening it and running the code again. What error do you get with that file?

xlsb file is just the same with the same error code

 

[brutusbeats]

xlsb file is just the same with the same error code

1.xlsm

Shared with Dropbox

www.dropbox.com

 

[brutusbeats]

.

 

[Joe4]

Pursuant to my last post, it sounds like you may have a corrupt workbook, so try recreating it.
See here: Fix VBA Run Time Error 32809 - Automate Excel