Subscript out of range error

[Osprey2]

The following two sub routines are used to remove some formulae from a sheet whilst it is being copied to another workbook, and to replace them after the copying.

Please Login or Register  to view this content.

The first sheet involved in this is "TapeBudgetA" and when the code reaches the copy line marked with xxx an error message "Subscript out of range" shows.
The sheet "Utilities" exists and the range contains the desired information so I cant understand where I have gone wrong. Can anyone help?
John

[6StringJazzer]

References to objects should be explicitly qualified. The default is different depending on context. You mention working with another workbook.

Without qualification, all references to Sheets will be assumed to be in the ActiveWorkbook, which may be different than the workbook containing the code.

• What workbook contains the code?
• What workbook contains the sheets TapeBudgetA and Utilities?
• What workbook is active when you run this code?

If the workbook with the code is the one containing those sheets, then you should either 1) guarantee that this workbook is active when you run the code, or 2) change Sheets to ThisWorkbook.Sheets.

[6StringJazzer]

P.S. I have updated your title because your title pointed to the Range but in reality the error is caused by the Sheets reference. Best to describe what you are seeing rather than an assumption of what the problem is.

[Osprey2]

6String Jazzer
Many thanks for your help. I changed the code to make sure there was no doubt which workbook/worksheet the range was in and all worked as wanted.
John

[6StringJazzer]

Good news. Glad I could help.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.