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

ItalianPlatinum

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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,985
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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

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

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

Well-known Member
Joined
May 16, 2017
Messages
1,985
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2017
Messages
566
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Right so have the workbook references are pretty much resolved but at the with sheet level it fails. see below:

VBA 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
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,985
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
When you say "it fails" what do you mean? What runtime error do you get?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,985
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

Forum statistics

Threads
1,182,140
Messages
5,933,869
Members
436,915
Latest member
Cygne volant

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