Macro will not set values across workbooks

[pethock]

Hi there,

I have a macro that generates multiple Excel sheets by looking at data on one sheet and then setting the values in a template accordingly (not copy/paste, but Worksheet1.Range(ABC123).value = Datasheet1.Range(XYZ789).value). The macro runs without issue across multiple PCs and versions of Excel, but for some reason one of my users cannot get the macro to run.

Everytime he tries to run the macro on his PC it will work up until the point values start to be set. At that point VBA returns Error 9: Subscript out of range. There are no special declarations or add-ins with the macro, all of the code and Excel settings are exactly the same as on the PCs that work (including the macro security level). We thought it might be his PC specifically, but he tried running the macro on his home PC and encountered the same result. And just to eliminate any variances, he sent over the exact same files he was using to another user and they were able to run the macro without issue.

Outside of the macro security level, are there any other settings that need to be enabled to allow macros to set values across workbooks? Or is there another reason why VBA would be returning Error 9? (We've also stepped through the code and all of the variables are intact through out as well)

Thanks for the help,
Pete

 

[Trevor G]

Pete welcome to the Forum, is it possible the pc is set to look at Excel in Compatibility mode and that the data range doesn't exist especially if are using ABC and XYZ as column letters.

Perhaps upload a copy of the code into your thread.

 

[Andrew Poulsom]

Welcome to MrExcel.

Are you referencing a workbook without its extension, eg Workbooks("Book1") instead of Workbooks("Book1.xls")? There's a write-up on that here:

File Extensions In VBA

 

[pethock]

Hi Andrew & Trevor, thanks for the quick replies! It was indeed the file extensions being visible causing the issue. After changing the setting to hide the extensions it was back to normal again. Thank you for the help! (And sorry for the confusion, ABC & XYZ were just examples; there were variable names used to reference the specific cells)

 

[Andrew Poulsom]

In my opinion you should always include the extension in a Workbook reference, rather than changing a Windows setting to cater for its absence.