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]

ok thanks will try that out tomorrow when I am back in the office.

 

[ItalianPlatinum]

Ok ran the debugger its looking in the prior workbook for that sheet. here is the longer code and error messages:

'Copy NAS File into NAS Compare
Workbooks.Open ("\\xxxx\xxx\xx\xx\xx\xx\xx\" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml")
Cells.Copy

    '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

2 Error messages:

1. With Sheets refers to workbook: 03-17.22 Ns.xml
2. Sheet "N Ns" does not exist in workbook '03-17-22 Ns.XML

 

[rlv01]

Ok ran the debugger its looking in the prior workbook for that sheet. here is the longer code and error messages:

'Copy NAS File into NAS Compare
Workbooks.Open ("\\xxxx\xxx\xx\xx\xx\xx\xx\" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml")
Cells.Copy

    '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

2 Error messages:

1. With Sheets refers to workbook: 03-17.22 Ns.xml
2. Sheet "N Ns" does not exist in workbook '03-17-22 Ns.XML

Your code expects there to be a worksheet named "N Ns" (With Sheets("N Ns"))
in your workbook named 03-17-22 Ns.XML. But as the debug code indicates, it is not in that workbook. You must investigate why not.

 

[ItalianPlatinum]

That isn't true -- this works fine for the users that dont have the file extension checked. For example; the user with the issue if he un-checks that box he is able to run it no problem, only when we check that box is where the VBA fails to "identify" that sheet. The workbook that is currently open that contains the VBA is where the sheets resides in.

 

[rlv01]

That isn't true -- this works fine for the users that dont have the file extension checked. For example; the user with the issue if he un-checks that box he is able to run it no problem, only when we check that box is where the VBA fails to "identify" that sheet. The workbook that is currently open that contains the VBA is where the sheets resides in.

I think there is something being missed here. Why would the worksheet name be dependent on whether or not a PC is configured to display file extensions? That choice only affects the workbook name.
Have you validated your user's results by doing your own testing. That show extension/don't show extension condition is something you can replicate on your own PC. What happens when you perform the test yourself?

 

[ItalianPlatinum]

I think it is having trouble going back to the source worksheet. Opens a file, copies the data then needs to go back to the VBA source to paste and cannot. I can redo the tests but i am going to just end up back to where I am now. I do not have excel 365 and do not know how to change in my version if you do I can do the tests tonight if not i need to redo Monday on the users PC.

 

[Peter_SSs]

Opens a file, copies the data then needs to go back to the VBA source to paste

In that case, try this change

With Sheets("N Ns")
With ThisWorkbook.Sheets("N Ns")

 

[ItalianPlatinum]

Ill rereview the VBA and reperform the tests on Monday. Pete I will also try that if the tests still fail. I will get back to you guys. Thanks for helping. hoping it is just something clerical in the VBA

 

[rlv01]

I think it is having trouble going back to the source worksheet. Opens a file, copies the data then needs to go back to the VBA source to paste and cannot. I can redo the tests but i am going to just end up back to where I am now. I do not have excel 365 and do not know how to change in my version if you do I can do the tests tonight if not i need to redo Monday on the users PC.

I do have Excel 365 and I spent some time testing this behavior. The SHOW/HIDE extension setting makes no difference in whether a worksheet that exists in a workbook can be referenced with a statement like

       With Sheets("N Ns")
            ' worksheet code here. 
        End With

Assuming the sheet exists in the workbook. I suspect that because you are relying on implied workbook references, you might be losing track of which workbook needs to be open.

 

[ItalianPlatinum]

I think i need to do add the workbook reference - on the old unmodified VBA i had this. and I think that could be the culprit. so going to revert back and retry for Monday. so i think just having the sheet you could be right its looking for that sheet in the most recent workbook i looked at so i need to define the other workbook i have open. so either way ill need to retest Monday and circle back.

'unprotect NAS file and Clear Contents
With Sheets("NAS Ns")
    .Unprotect
    .Cells.ClearFormats
    .Cells.Delete
End With

'Copy NAS File into NAS Compare
Workbooks.Open ("xxxx\xxx\xxx\xxx\xxx\xxxx\xxxx\" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml")
Cells.Copy

With Workbooks("Ns Compare").Sheets("NAS 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

Workbooks.Open("\\xxxxx.xxx.xxxx.xxxx\xxxxxxx\xxxx\xxxxx\xxxxx\xxxxx\xxxxx\" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml").Close SaveChanges:=False

'Fomulas for Review (NAS)
With Workbooks("Ns Compare").Sheets("Ns COMPARE")
    lr = .Cells(rows.count, "A").End(xlUp).row