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")

 

[Peter_SSs]

With Workbooks("Ns Compare").Sheets("NAS Ns")

If the vba code is in the "Ns Compare" workbook then the above line of code would be doing the same thing as the one I suggested in post #27
The post #27 code has the advantage that it would still work if you happened to rename the "Ns Compare" workbook.

If you do not specify which workbook the sheet is in, as was the case with the following line, then the code will be expecting to find the sheet "NAS Ns" in whatever the "active" workbook is - risky when multiple workbooks are open.

With Sheets("NAS Ns")

 

[ItalianPlatinum]

Pete that is very helpful I actually use the reference of with sheet quite a bit. do you advise it is safer to modify those instance with your post #27?

 

[Peter_SSs]

do you advise it is safer to modify those instance with your post #27?

Any time that there could be doubt about the workbook, it is best to remove that doubt.

Another way would be this sort of structure (I've just used dummy code) then each sheet only needs to be identified once.

Dim wsComp As Worksheet, wsNNs As Worksheet

With ThisWorkbook
  Set wsComp = .Sheets("Ns COMPARE")
  Set wsNNs = .Sheets("N Ns")
End With

With wsComp
  .Range("A1").Value = wsNNs.Range("J10").Value
End With

With wsNNs
  .Columns("A").ColumnWidth = 10
End With

 

[ItalianPlatinum]

let me try that. so i really only need to ever modify one section instead of my whole code!

 

[ItalianPlatinum]

It worked successfully.

I also found out why it initially failed not only was the full workbook not defined in this with sheet (thisworkbook) corrected that. but the user saved the file as a different name and it was falling down at this step.

'Use either workbook extension to avoid file extension option issue
On Error Resume Next
Set wbNCOMP = Workbooks("NAS COMPARE")
Set wbNCOMP = Workbooks("NAS COMPARE.xlsm")
On Error GoTo 0

'Set Range for Comp, & FWD date
Comp = wbNCOMP.Sheets("Ns COMPARE").Range("Comp")

is there a way avoid that in any way?

 

[Peter_SSs]

the user saved the file as a different name ...
is there a way avoid that in any way?

If it is the workbook that the code is not in that has been renamed then that is not so straight forward to cope with.
Is there something in that other workbook that will not change? Examples
- Is there always a worksheet called, say, "Ns COMPARE" or "Imported Data" or ...?
- Is there always a heading of, say, "Entry Date" in row 1 of the first worksheet?
- Is there always some part of the workbook name that will never change (eg starts with "NAS ")?
etc

If there is some constant in that other open workbook then there is likely a way without relying on knowing the exact workbook name.

 

[ItalianPlatinum]

Yea the sheet name (Ns Compare) will be a constant is is locked (protected). In the current state the user CANNOT in theory save it as and run how they please as it will cause those errors. So sounds like it isnt quite as cut and dry as i assumed in my head