Error while copying cells in another normal xls workbook when VBA Macro sheet is open

[hiravibk]

Hi,

I am facing a peculiar problem. I have built excel VBA macros for various functionalities like create dynamic excel workbooks based upon userform input values and some manipulations as required.

Now, when this VBA Macro excel sheet is open (not running). I am not able to copy any cells from another normal excel workbook(.xls). Getting "Subscript Out Of Range" with no error number. Attached for reference.

Could you please guide me here? Let me know if something is not clear.

Thanks In Advance,
Ravi

Other Info: Excel 2010, Win7

 

[Jerry Sullivan]

Hi and Welcome to the Board,

I'm not clearly understanding what you mean by "this VBA Macro excel sheet is open". Do you mean a mean the Workbook with macros is open, or a Worksheet with event procedures is the Active Sheet?

If the error is directly from the Excel's debugger (not a MsgBox from the code's error handler), then a good place to start tracking down the problem is to click the "Debug" button. This will take you a highlighted line of code at which the execution has stopped. That line might refer to a worksheet that has been renamed or deleted which would generate a Subscript Out Of Range error.

If there's no Debug button, you could try Ctrl-Break while the error message is displayed to try to force a break to the debugger.

 

[hiravibk]

Thanks Jerry for your reply. Let me explain my situation again.

I have two excels objects opened 1) tool.xlsm, where in VBA macro code resides to do some manipulation. 2) Now, I open the existing normal xls file (somedata.xls). And while copying any cell data (say Cell A1, CTRL+C) from somedata.xls, boom! "Subscript Out Of Range" Error is thrown, caption of dialog box is Microsoft Visual Basic Applications. Yes, there is no Debug button in the message box, only OK and Help.

I tried to get into debug using CTRL+Break but it is not working, message box just disappears. and nothing else happens.

Then re reading your reply multiple times made me realize that ("refer to a worksheet that has been renamed or deleted") -

In my VBA code,
1) I am adding a new workbook (somedata1.xls)
2) Copying sheets from tool.xlsm(a,b)
3) deleting the default sheets (1,2,3).
4) do some manipulations and close the workbook.

So here the ones I was planning to copy content was from somedata1.xls (which is present in VBA project explorer) something just created by macro Or just opened a blank workbook and entered some details into Sheet1 and try to copy it. In both occassion, I hit Subscript out of Range Error.

So I created blank new workbook, renamed default sheets (say a, b, c), saved as new.xls and NOW, I am able to copy cell contents from it.

So here is my question to you Jerry,
1) If the above stated explaination is correct. How to get rid (clear) of newly created xls being clogged up in Project Explorer.? I do set the object = nothing once all the processing done in VBA code.
2) I will not able to copy the contents from default numbered sheets while the macro is open?

Thanks Again!
Ravi

 

[hiravibk]

Jerry,

I got the solution. It is a tricky miss from my side.

There are 3 macros in VBA code with userforms and one of them had the shortcut, CTRL+c to invoke it. So it was trying to invoke that UserForm and tried to load a values from a sheet(lookup) present in xlsm file. Now when I am trying to copy it from normal xls file with CTRL+c, its erroring which is not present in the file from which I am trying to copy contents using CTRL+c.

And I changed the settings Tools -> Options -> General -> Break On all Errors to capture this error.

Other thing I learnt is, not to give worksheets reference like set ws = worksheets("lookup") instead with workbook.worksheets("SheetName") as that is more specific.

Thanks all for help,
Ravi

 

[Jerry Sullivan]

Ravi, Glad to hear you sorted that out. Thanks for posting your resolution.