Excel 365 VBA Error

[ItalianPlatinum]

Hello this will be a stretch but worth a shot as I am out of options. I have a macro (XLSM) that opens a few files, copies data, pastes into a main workbook called "N Compare", then closes said files used to copy from.

Issue I have is one individual from my team cannot run it. He receives Subscript out of range and is the only user on Microsoft 365. He keeps getting it on the below lines, each one. If i comment them out he can run through no problem but once i introduce one of them the Microsoft 365 user cannot support it. is there a known issue or limitation I am just not factoring in? or any options I have at my disposal?

'Set Range for Composite, & FFWD date
'Composite = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("Composite")
'FFWD = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("FFWD")
'Today = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("Today")

 

[ItalianPlatinum]

I was actually going to not solution via VBA and in turn kick it back to the user (to adjust their PC settings to accommodate). but will keep it in my arsenal if it becomes a more common theme so far on a team of 15 only have exposure with 1 user.

 

[rlv01]

I was actually going to not solution via VBA and in turn kick it back to the user (to adjust their PC settings to accommodate). but will keep it in my arsenal if it becomes a more common theme so far on a team of 15 only have exposure with 1 user.

This is not a situation unfamiliar to me as I have many workbooks in active use by various teams. It's up to you of course, but IMHO, forcing users to change their PC settings to accommodate your code instead of the reverse is not a viable long-term solution.

 

[ItalianPlatinum]

Yea i mean it is a valid point so users can have whatever settings they wish and still have no impact. I guess you are now talking me into changing the vba....

 

[Peter_SSs]

IMHO, forcing users to change their PC settings to accommodate your code instead of the reverse is not a viable long-term solution.

I agree.

 

[ItalianPlatinum]

Ok I may need some help. I didn't realize I reference the workbook quite a bit so changing all to .xlsm is sort of a pain. So I added Pete's code. It seems to be working up until I run into any use of "With sheets" in reference to a sheet in that workbook. That is where it falls down again. How can I resolve?

 

[rlv01]

I would not have expected any "With Sheets" code to be affected by adding the .xlsm extension to the Workbook reference so I'd need to see a code example of what you mean and the specific error in question.

Keep in mind that @Peter_SSs 's code example assigns a workbook variable wbNCOMP to reference the workbook, so unless you have gone around and replaced all references to Workbooks("N COMPARE") with wbNCOMP you would have trouble - but absent a code example, I'm just speculating. Is there any reason you cannot just do a project-wide search and replace? Searching for Workbooks("N COMPARE") and replacing it with Workbooks("N COMPARE.xlsm")?

 

[ItalianPlatinum]

Right so have the workbook references are pretty much resolved but at the with sheet level it fails. see below:

With Sheets("N Ns")
.Range("A1").PasteSpecial Paste:=xlPasteValues
.Range("1:1").AutoFilter
.Range("B2:B" & Cells(rows.count, "B").End(xlUp).row).TextToColumns , xlDelimited
.Protect AllowFormattingColumns:=True, DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

 

[rlv01]

When you say "it fails" what do you mean? What runtime error do you get?

 

[ItalianPlatinum]

subscript ouf of range

 

[rlv01]

Then it cannot find worksheet "N Ns". Place this temporary debug code above the code you posted earlier and report back on what the message boxes say.

    'Start debug code
       MsgBox """With Sheets"" refers to workbook: " & Sheets(1).Parent.Name, vbOKOnly Or vbInformation, Application.Name
       Dim WS As Worksheet
       On Error Resume Next
       Set WS = Sheets("N Ns")
       On Error GoTo 0
       If WS Is Nothing Then
           MsgBox "Sheet ""N Ns"" does not exist in Workbook '" & Sheets(1).Parent.Name & "'", vbOKOnly Or vbExclamation, Application.Name
           Exit Sub
       End If
    'End debug code
    
    With Sheets("N Ns")
        .Range("A1").PasteSpecial Paste:=xlPasteValues
        .Range("1:1").AutoFilter
        .Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).TextToColumns , xlDelimited
        .Protect AllowFormattingColumns:=True, DrawingObjects:=True, Contents:=True, AllowFiltering:=True
    End With