Excel 365 VBA Error

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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?

VBA Code:
'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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,497
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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.
VBA Code:
With Sheets("NAS Ns")
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,497
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
let me try that. so i really only need to ever modify one section instead of my whole code!
 

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.

VBA Code:
'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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,497
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,182,145
Messages
5,933,889
Members
436,916
Latest member
LonN90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top