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]

Welcome to the Board!

I do not see anything in there that would not work in newer versions of Excel (typically, things that work in older versions work in the newer versions too).
Chances are, the issue is with the computer configuration (maybe Macros/VBA are being disabled), data of the file you are testing on, missing VBA libraries (though I am not seeing anything special in your code that would require that), or network configuration (if interacting with directories, which you are not doing here).

How exactly is it NOT working?
Are you getting error messages (if so, what)?
Or is it just not doing what you expect?
Have you tried stepping through the code line-by-line while monitoring your sheet to watch what happens?

 

[brutusbeats]

Welcome to the Board!

I do not see anything in there that would not work in newer versions of Excel (typically, things that work in older versions work in the newer versions too).
Chances are, the issue is with the computer configuration (maybe Macros/VBA are being disabled), data of the file you are testing on, missing VBA libraries (though I am not seeing anything special in your code that would require that), or network configuration (if interacting with directories, which you are not doing here).

How exactly is it NOT working?
Are you getting error messages (if so, what)?
Or is it just not doing what you expect?
Have you tried stepping through the code line-by-line while monitoring your sheet to watch what happens?

I don't actually know what to change anything to. This is the first error

 

[Joe4]

Ah, I am guessing that in this new version, you have "Option Explicit" turned on (which is actually a good thing), and you do not in your old one. Doing this requires you to declare all variables before using them. You have not declared "sh".

So add a line near the top with your other variable declarations like this:

Dim sh as Worksheet

Note that if you have other undeclared variables, you will need to declare them too.
If you try compiling the workbook, it will find all those undeclared variables for you (one at a time).

See here for more information on Option Explicit:

How to use Option Explicit Statement in VBA

Option explicit is a statement that you can use at the beginning of a module to force yourself to declare all the variables. When you add this statement VBA

excelchamps.com

 

[MARK858]

As that is not the error you normally get when you don't define a variable (you normally get the error message below)

Can you also click Tools - References and check the list to see if any have "MISSING" next to them

 

[Joe4]

As that is not the error you normally get when you don't define a variable (you normally get the error message below)
View attachment 96155

Can you also click Tools - References and check the list to see if any have "Missing" next to them

Good catch.

They should probably at least have the following libraries (or similar versions of these libraries) selected:

 

[brutusbeats]

 

[Joe4]

OK, did you also try declaring those variables, like I said?

It appears that it might be mistakenly thinking "sh" is something else.
Declaring it as a worksheet might help clear that up.

 

[brutusbeats]

Yes i did, now this has come up

 

[Joe4]

Do you actually have a sheet with that EXACT name?
Note that anything as minor as an extra space in the sheet name will cause it NOT to match.